Pivot Cell into Columns

Question

I have data that looks like this:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Fruit TypeRow 1.&nbsp; &nbsp; &nbsp; Apple=30% | Banana=20% | Orange=50%Row 2.&nbsp; &nbsp; &nbsp; Apple=100%&nbsp;Row 3.&nbsp; &nbsp; &nbsp; Banana=20% | Apple=70%&nbsp;and I would like it to look like this:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Apple&nbsp; &nbsp; &nbsp; &nbsp; Banana&nbsp; &nbsp; &nbsp; &nbsp;OrangeRow 1.&nbsp; &nbsp; &nbsp; 30%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50%Row 2.&nbsp; &nbsp; &nbsp; 100%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0%Row 3.&nbsp; &nbsp; &nbsp; &nbsp;70%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0%&nbsp;Any advice would be appreciated.&nbsp;&nbsp;&nbsp;

zubair_muhammad · Accepted Answer

HI Crow2525&nbsp;
&nbsp;
Try this
Please see attached excel file's Query Editor for steps (with your sample data) as well
&nbsp;
let
    Source = Excel.CurrentWorkbook(){[Name="TableName"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit Type", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Fruit Type", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Fruit Type.1", "Fruit Type.2", "Fruit Type.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fruit Type.1", type text}, {"Fruit Type.2", type text}, {"Fruit Type.3", type text}}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"Index"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Percentage.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Value.1]), "Value.1", "Value.2")
in
    #"Pivoted Column"

Forum Discussion

Pivot Cell into Columns

2 Replies

Related content

Power BI Pivot Columns ?

Pivot issue

Pivot / Unpivot?

Power Query Pivoting columns issue

How to pivot Multiple Columns