Forum Discussion
Help with setting up a Pivot Column
Please help! I need the values in "Attribute" to become new column headers, and the data in the Values column to become the values under the new columns. No data should be changed to averages/counts. I've tried several times and can't get it to work right. Please help!
I have....
| Name | Attribute | Value |
| John | Date | March 1, 2023 |
| John | Purchase | Apple |
| John | Amount | $0.50 |
| John | Date | March 2, 2023 |
| John | Purchase | Sandwich |
| John | Amount | $4.75 |
| Susan | Date | March 1, 2023 |
| Susan | Purchase | Sandwich |
| Susan | Amount | $4.75 |
| Susan | Date | March 5, 2023 |
| Susan | Purchase | Fish |
| Susan | Amount | $12.00 |
I want...
| Name | Date | Purchase | Amount |
| John | March 1, 2023 | Apple | $0.50 |
| John | March 2, 2023 | Sandwich | $4.75 |
| Susan | March 1, 2023 | Sandwich | $4.75 |
| Susan | March 5, 2023 | Fish | $12.00 |
Hi bryantw,
another approach.
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXJJLEkFUr6JRckZCoY6CkYGRsZKsTpw+YBSoERiMUiNY0FBTiqynGNufmleCZChYqBnaoAsg2KqEV5TgxPzUsozkzOwG2yiZ24KlgkuLU7E616YAlxGw+SJNdsUv9lumcW4zDU00jMAhkYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Attribute = _t, Value = _t]), Attributes = List.Buffer(List.Distinct(Source[Attribute])), StepBack = Source, PivotedColumn = Table.Pivot(StepBack, Attributes, "Attribute", "Value", each _), Ad_Table = Table.AddColumn(PivotedColumn, "t", each Table.FromColumns( List.Combine(List.Transform(Attributes, (x)=> {Record.Field(_, x)} )), Attributes), type table), #"Removed Other Columns" = Table.SelectColumns(Ad_Table,{"Name", "t"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "t", Attributes), #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Name", Order.Ascending}, {"Date", Order.Ascending}}) in #"Sorted Rows"
6 Replies
Hi bryantw,
another approach.
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXJJLEkFUr6JRckZCoY6CkYGRsZKsTpw+YBSoERiMUiNY0FBTiqynGNufmleCZChYqBnaoAsg2KqEV5TgxPzUsozkzOwG2yiZ24KlgkuLU7E616YAlxGw+SJNdsUv9lumcW4zDU00jMAhkYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Attribute = _t, Value = _t]), Attributes = List.Buffer(List.Distinct(Source[Attribute])), StepBack = Source, PivotedColumn = Table.Pivot(StepBack, Attributes, "Attribute", "Value", each _), Ad_Table = Table.AddColumn(PivotedColumn, "t", each Table.FromColumns( List.Combine(List.Transform(Attributes, (x)=> {Record.Field(_, x)} )), Attributes), type table), #"Removed Other Columns" = Table.SelectColumns(Ad_Table,{"Name", "t"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "t", Attributes), #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Name", Order.Ascending}, {"Date", Order.Ascending}}) in #"Sorted Rows"Hi bryantw ,
How about the following?
Before:After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough).
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXJJLEkFUr6JRckZCoY6CkYGRsZKsTpw+YBSoERiMUiNY0FBTiqynGNufmleCZChYqBnaoAsg2KqEV5TgxPzUsozkzOwG2yiZ24KlgkuLU7E616YAlxGw+SJNdsUv9lumcW4zDU00jMAhkYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Attribute = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Attribute", type text}, {"Value", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Attribute] = "Date")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name", "Attribute"}, {{"Grouping", each _, type table [Name=nullable text, Attribute=nullable text, Value=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.Buffer(Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Attribute", "Value", "Index"}, {"Name", "Attribute", "Value", "Index"})), #"Merged Queries" = Table.Buffer(Table.NestedJoin(#"Changed Type", {"Name", "Attribute", "Value"}, #"Expanded Custom", {"Name", "Attribute", "Value"}, "Expanded Custom", JoinKind.LeftOuter)), #"Expanded Expanded Custom" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom", {"Index"}, {"Expanded Custom.Index"}), #"Filled Down" = Table.FillDown(#"Expanded Expanded Custom",{"Expanded Custom.Index"}), #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Attribute]), "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Expanded Custom.Index"}) in #"Removed Columns"Note, this approach is relying heavily on that your data is in the correct order. Otherwise we wouldn't know which rows belong to which date. The Table.Buffer() function keeps that order even throughout that merge (something I learned myself just now 🙂 )
Let me know if this solves your issue!
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Thanks both of you for your replies. I ALMOST got the approach from dufoq3 to work. The data types and columns all look good, but the entries after the first person get mixed up. In my example, John's entries are good, but the date/amounts for Susan are wrong. I think it has to do with the dates. The original data table has all of one person's entries together in chronological order. Then they restart with the next person. The Power Query syntax is all new to me, so I couldn't troubleshoot it.
What would still be MOST helpful for me, is if you can walk me through the steps using the Power Query Editor ribbon, that generated your code. And point out any steps that I'd need to manually write the code. Then, hopefully, I could do this on my own in the future.
Here's a link to my data. If your solution relies on ALL the dates being in order, then this could be the problem.
