Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everybody!
I really appreciate your help to see how I can solve this. I have an excel file with this data:
At the end I have to represent the data like this: for each line each serial
How can I do it?
I did a test: unpivot, but it's not what I need 😞
And another test with PO Line and Serial column split: but I can't get what I want to show either.
I appreciate your ideas and support.
Regards,
Lina
Solved! Go to Solution.
This can be optimized, but gives you the general idea. Note the inconsistency - PO Line values are only separated by comma, Serial values also have spaces.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYzBCsMwDEN/Zfjsge3EsXceYZdtDaUwRsn//8bs0UsFEpIOb9+hiN1sHf26fZ6AsK1SR78vlGoeT38tkVW9kCsLx0j39yASmJgIFz0jHt8DwXxGmJCWRKBgwYqKLZaZ4sUaR5hnq9n+n8KcPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DO Final" = _t, #"Reference / Subject e-mail" = _t, Priority = _t, #"SAP PO" = _t, #"PO Line" = _t, Serial = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([PO Line],","),Text.Split([Serial],",")})),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Record.FromList([Custom],type [#"PO Line"=text, Serial=text])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"DO Final", "Reference / Subject e-mail", "Priority", "SAP PO", "Custom.1"}),
#"Expanded Custom.1" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom.1", {"PO Line", "Serial"}, {"PO Line", "Serial"})
in
#"Expanded Custom.1"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
This can be optimized, but gives you the general idea. Note the inconsistency - PO Line values are only separated by comma, Serial values also have spaces.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYzBCsMwDEN/Zfjsge3EsXceYZdtDaUwRsn//8bs0UsFEpIOb9+hiN1sHf26fZ6AsK1SR78vlGoeT38tkVW9kCsLx0j39yASmJgIFz0jHt8DwXxGmJCWRKBgwYqKLZaZ4sUaR5hnq9n+n8KcPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DO Final" = _t, #"Reference / Subject e-mail" = _t, Priority = _t, #"SAP PO" = _t, #"PO Line" = _t, Serial = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([PO Line],","),Text.Split([Serial],",")})),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Record.FromList([Custom],type [#"PO Line"=text, Serial=text])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"DO Final", "Reference / Subject e-mail", "Priority", "SAP PO", "Custom.1"}),
#"Expanded Custom.1" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom.1", {"PO Line", "Serial"}, {"PO Line", "Serial"})
in
#"Expanded Custom.1"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
User | Count |
---|---|
84 | |
79 | |
62 | |
62 | |
51 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |