Forum Discussion
Power Query - csv 2 columns with delimited data - How do I transform this?
Hello Everyone, I am a bit of a newb to power query but need some help. Below is data that is pulled out from a CSV file. For simplicity sake, we can focus on the 2nd and 3rd columns in the image (Material, and diverted). Both of these columns contains pipe delimited data. How can I transform both of these columns so that I can get sum totals respectively at a row level?
Basically the position of the pipe delimiter is where the data was entered on the form. Example id 33269 - Concrete = 180, Trash was blank, other blank. ID - 33313 Dirt/Inerts = blank, Green = 120. etc.
This data will be dynamic, as the data coming in may have more or less selections causing there to be more pipe delimited values across each column. I tried some different ways to split the columns to row and to column data, which works if there is only 1 delimited column to work with. But in this instance there are multiple columns that are pipe delimited.
Any suggestions appreciated. Thanks
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"
4 Replies
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"
