Forum Discussion
Power Query - csv 2 columns with delimited data - How do I transform this?
Use this. Solution file uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuUq-9AK_HpNZqJTw?e=xnmxKe
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", type text}}, "en-US"), {{"Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"), #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if [Attribute]="Total Tons Service Material" then "Diverted-"&[Value] else [Value]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}), #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Attribute] = "Total Tons Service Material")), #"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each ([Attribute] = "Tons Diverted")), Custom1 = Table.FromColumns(Table.ToColumns(#"Filtered Rows1")&{#"Filtered Rows2"[Custom]},{"ID","Column2","Total Tons Service Material","Tons Diverted"}), #"Removed Columns1" = Table.RemoveColumns(Custom1,{"Column2"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Total Tons Service Material"]), "Total Tons Service Material", "Tons Diverted") in #"Pivoted Column"
Taking the example of first row, do you want this to be split into 3 rows or are you just seeking the sum for the row...
If you can give a sample output of just 1 row, that would be enough for us to work out a solution for you.
Thanks for the response. I would want a sum of each Material per the ID. So the end output would look something like this:
ID Diverted -Concrete Diverted -Trash Diverted -Other Diverted -Dirt/Inerts Diverted -Green Diverted -MixedO 33269 180 33313 120 33319 120 33324 110 33342 120 Ultimately i will want to tally up the amounts per ID. I am open to a different format if easier. I am just having difficulty on how to handle 2 columns of dilemeted data that have relationship by the position of the dilimit. On top of the dynamic range of columns that would appear based on the front end selections of the materials
Apologies to the table formatting, it blew out the formatting and looks mashed up
Use this. Solution file uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuUq-9AK_HpNZqJTw?e=xnmxKe
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", type text}}, "en-US"), {{"Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"), #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if [Attribute]="Total Tons Service Material" then "Diverted-"&[Value] else [Value]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}), #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Attribute] = "Total Tons Service Material")), #"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each ([Attribute] = "Tons Diverted")), Custom1 = Table.FromColumns(Table.ToColumns(#"Filtered Rows1")&{#"Filtered Rows2"[Custom]},{"ID","Column2","Total Tons Service Material","Tons Diverted"}), #"Removed Columns1" = Table.RemoveColumns(Custom1,{"Column2"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Total Tons Service Material"]), "Total Tons Service Material", "Tons Diverted") in #"Pivoted Column"
