Transforming data from 1 date row with lots of columns to different date rows with less columns

[KobeDel]

Hello,

I have a dataset like this. But with a lot more columns. At this moment, there is in the column name, the machine name and the categorie. Machine name is always 3 characters.

	AG5 startup	AG7 startup	ML1 startup	AG5 folding	AG7 folding	ML1 folding
1/05/2025	50	30	0	10	30	0
2/05/2025	20	10	35	35	45	20
3/05/2025	85	65	35	24	36	0
4/05/2025	48	87	61	89	14	0
5/05/2025	14	67	25	24	21	0

I want to transform it like this, so that it is more easy to make a power bi dashboard from it with filters and categories.

Any tips on how to handle/start this transformation? Many thanks in advance.

	kg	machine	categorie
1/05/2025	50	AG5	startup
1/05/2025	30	AG7	startup
1/05/2025	10	AG5	folding
1/05/2025	30	AG7	folding
2/05/2025	20	AG5	startup
2/05/2025	10	AG7	startup
2/05/2025	35	ML1	startup
2/05/2025	35	AG5	folding
2/05/2025	45	AG7	folding
2/05/2025	20	ML1	folding
…

[KobeDel]

Didn't know it was that easy. Thank you very much for the fast response!!

[p45cal · Accepted solution]

See attached a basic start:

Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3BDcAgCAXQXTybCAjWzmLcf436bSsc/AF94BiJuJAVIbGUk9GKisDh0M28qAQqLuwP/R42rgF3RDtOFFXzxRqsdgxcGGBUN/5Rtxbsvm+wcvYKv3Y+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"AG5 startup" = _t, #"AG7 startup" = _t, #"ML1 startup" = _t, #"AG5 folding" = _t, #"AG7 folding" = _t, #"ML1 folding" = _t]),
    CT = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    UnpivotOtherColumns = Table.UnpivotOtherColumns(CT, {"Date"}, "Attribute", "kg"),
    SplitColumn = Table.SplitColumn(UnpivotOtherColumns, "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Machine", "categorie"}),
    CT2 = Table.TransformColumnTypes(SplitColumn,{{"kg", type number}}),
    Filter = Table.SelectRows(CT2, each ([kg] <> 0)),
    ReOrderolumns = Table.SelectColumns(Filter,{"Date", "kg", "Machine", "categorie"})
in
    ReOrderolumns