Pivot Cell into Columns

[Crow2525]

I have data that looks like this:

                 Fruit Type

Row 1.      Apple=30% | Banana=20% | Orange=50%

Row 2.      Apple=100% 

Row 3.      Banana=20% | Apple=70%

and I would like it to look like this:

                 Apple        Banana       Orange

Row 1.      30%           20%            50%

Row 2.      100%         0%              0%

Row 3.       70%          20%            0%

Any advice would be appreciated.

[Zubair_Muhammad · Accepted solution]

HI @Crow2525 

Try this

Please see attached excel file's Query Editor for steps (with your sample data) as well

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"

[Crow2525]

Thanks, I've applied your solution to my data with success, which had substantially more types of 'fruit'.

Appreciate your time and the provided excel file.