Forum Discussion
Solved
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 (Ma...
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.
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"