Forum Discussion

onedollar's avatar
onedollar
Regular Visitor
4 years ago
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...
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"