Transpose and Analyze Data

[collint20]

How do I transpose and analyze data automatically using PowerQuery or PowerBI? How can I use PowerQuery or PowerBI to output a new Query Table based on the following Raw Data Table?

Raw Data Table

Person	topping 1	topping 2	topping 3
Mike	Pepperoni	Sausage	Artichoke
Chris	Green Onion	Olives	Ham
Stacy	Sausage	Olives	Garlic
Jack	Ham	Olives	Garlic
Jill	Pineapple	Pepperoni	Artichoke
Peter	Pineapple	Green Onion	Sausage
Nate	Artichoke	Green Onion	Pepperoni
Greg	Pepperoni	Olives	Garlic
Kim	Artichoke	Green Onion	Olives
Lisa	Sausage	Olives	Pineapple

Query Table (Data reorganized)

Olives	Green Onion	Sausage	Ham	Pineapple	Artichoke	Pepperoni	Sausage	Garlic
Chris	Chris	Stacy	Chris	Jill	Mike	Mike	Mike	Stacy
Stacy	Peter	Lisa	Jack	Peter	Jill	Jill	Peter	Jack
Jack	Nate			Lisa	Nate	Nate		Greg
Greg	Kim				Kim	Greg
Kim
Lisa

[Greg_Deckler · Accepted solution]

@collint20 I would highly recommend unpivoting your topping columns like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBNCsMgEEavUlznEqWLlP4lkKW4GGRIhhgVNYXevhrSBiXtTj/fzLyRc3anEVnFWrQWndEUzx3MHvqUHl0gOZhIiIqz0+DIx7R2iPrQaDI63hpFT0zxGaYF6wLIV9bmi9TgFMmFuoAc16IfACmVxEgjWKtKyVytxYCuoHPNj02iHxDy7Up4G5Tw+NYX0/eErzT9bbrWJPRGHvZ/aFtAiDc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"topping 1" = _t, #"topping 2" = _t, #"topping 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"topping 1", type text}, {"topping 2", type text}, {"topping 3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Person"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Topping"}})
in
    #"Renamed Columns"

This should allow you to create a matrix visualization that looks like your second table and really be the best thing to work with in terms of Power BI. That said, I'll see if I can get it into the final form you are looking for but may take a bit.

[Greg_Deckler · Accepted solution]

@collint20 This is pretty close:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBNCsMgEEavUlznEqWLlP4lkKW4GGRIhhgVNYXevhrSBiXtTj/fzLyRc3anEVnFWrQWndEUzx3MHvqUHl0gOZhIiIqz0+DIx7R2iPrQaDI63hpFT0zxGaYF6wLIV9bmi9TgFMmFuoAc16IfACmVxEgjWKtKyVytxYCuoHPNj02iHxDy7Up4G5Tw+NYX0/eErzT9bbrWJPRGHvZ/aFtAiDc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"topping 1" = _t, #"topping 2" = _t, #"topping 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"topping 1", type text}, {"topping 2", type text}, {"topping 3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Person"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Topping"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Topping"}, {{"Person", each _, type table [Person=nullable text, Topping=text]}}),
    #"Expanded Person" = Table.ExpandTableColumn(#"Grouped Rows", "Person", {"Person"}, {"Person.1"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Person", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],6)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Topping]), "Topping", "Person.1"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in
    #"Removed Columns2"

[collint20]

Perfect solution !

I created tables in the report view using the query you created, and acheived the exact result I was looking for !

Did the trick !

Thank you so much !

Collin

[Greg_Deckler · Accepted solution]

@collint20 I would highly recommend unpivoting your topping columns like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBNCsMgEEavUlznEqWLlP4lkKW4GGRIhhgVNYXevhrSBiXtTj/fzLyRc3anEVnFWrQWndEUzx3MHvqUHl0gOZhIiIqz0+DIx7R2iPrQaDI63hpFT0zxGaYF6wLIV9bmi9TgFMmFuoAc16IfACmVxEgjWKtKyVytxYCuoHPNj02iHxDy7Up4G5Tw+NYX0/eErzT9bbrWJPRGHvZ/aFtAiDc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"topping 1" = _t, #"topping 2" = _t, #"topping 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"topping 1", type text}, {"topping 2", type text}, {"topping 3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Person"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Topping"}})
in
    #"Renamed Columns"

This should allow you to create a matrix visualization that looks like your second table and really be the best thing to work with in terms of Power BI. That said, I'll see if I can get it into the final form you are looking for but may take a bit.

[collint20]

How do I change your "source" to my local file "source". I tried replacing your parameters with mine, but each time I get an error.

Source = Excel.Workbook(File.Contents("C:\Users\colli\OneDrive\Desktop\KorbieDev\Pizza.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

[Greg_Deckler]

@collint20 Hmm, should be: 

let
    Source = Excel.Workbook(File.Contents("C:\Users\colli\OneDrive\Desktop\KorbieDev\Pizza.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Person", type text}, {"topping 1", type text}, {"topping 2", type text}, {"topping 3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Person"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Topping"}})
in
    #"Renamed Columns"