Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Crow2525
Helper I
Helper I

Pivot Cell into Columns

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.

  

 

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

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"

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

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"

Regards
Zubair

Please try my custom visuals

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.