Forum Discussion
Solved
Transpose and Analyze Data
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 |
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 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"
5 Replies
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.
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],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"
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"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
