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 Tabl...
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"
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"