Need Help: Adding rows based on cell values

[ugh]

Say I have a table like:

week #	blah0	blah1	blah2
41	2	1	1
42	1	3	0
43	0	2	1

Would it be possible to refactor it into the following using Power Query? If so, what would the code look like? I am very stumped.

id	week #	category
1	41	blah0
2	41	blah0
3	41	blah1
4	41	blah2
5	42	blah0
6	42	blah1
7	42	blah1
8	42	blah1
9	43	blah1
10	43	blah1
11	43	blah2

Any help is much appreciated! 🙂

[ImkeF · Accepted solution]

Hi @ugh ,
the code could look like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFU0lEyAmJDMI7VAQrBuMZAbAARgjBhKmNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"week #" = _t, blah0 = _t, blah1 = _t, blah2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"week #", Int64.Type}, {"blah0", Int64.Type}, {"blah1", Int64.Type}, {"blah2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"week #"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each {1..[Value]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value", "Custom"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

[ugh]

So quick! Thank you this works! 😁