Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
101 | |
97 | |
66 | |
59 |