Help with setting up a Pivot Column

[bryantw]

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

[bryantw]

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.

[dufoq3]

@bryantw, we only can work with sample data. If your data is different - share it with us. It would be also good to post screenshots of the step before trouble starts and also step with error or whe it starting to go "wrong way"

[bryantw]

Here's a link to my data.  If your solution relies on ALL the dates being in order, then this could be the problem. 

Meal Purchase Data.xlsx

[dufoq3]

@bryantw, no, my query doesn't rely on dates order. It works also with your data. Here you have new sample

Edit 27/02/2024: Added 2nd version of code

Source:

Result:

Query is the same (except sample data)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1dVNa4MwHAbwryKe48iLUeNtdT10rCCzsEMpI7hsyuoL1g767WeHiabVUjp6EARLnn+JP56g67X5XCS5CcwnXovmhuwHAzcXxLaBsE+pD6G5AWrsMY6LfV43v2xivS2joB8uapE1t6Xg2/5yJKqfNP8yQlGlxUez8LLP46Q/EfJDJvLaWB3K40OElSj5Qds2a3e1CED9YMa3PI+Pf/IgoP1EgrAGsuF0QAQ4AyBXL8iZjscB7oCH6v2Q6XgY8AY8+nkjbDIehgH7S6L9jo+/EpqrFcm5joQhscLVItDiFhUkabnTggssOTLuUpsrGNLWOxe6igXpbaxFLN6DKrsrDGA6Yruusltt6hzeCSaP4rnLBogOwoh+FtEUS3NVVJRJykdqQz5W3yo12PEQpNZrOA/0vPXNI6PrTmaXgHLmglA9giJi4J04OiP1ZLenRqwZEZuu0YGAjRTZQ7o+cu9rnFWCf3/yXf0PJwLjTNJ+506VTG/SmXCTFBBzs/kF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Attribute = _t, Value = _t]),
    v1_Attributes = List.Buffer(List.Distinct(Source[Attribute])),
    v1_StepBack = Source,
    v1_PivotedColumn = Table.Pivot(v1_StepBack, v1_Attributes, "Attribute", "Value", each _),
    v1_Ad_Table = Table.AddColumn(v1_PivotedColumn, "t", each Table.FromColumns(
      List.Combine(List.Transform(v1_Attributes, (x)=> {Record.Field(_, x)} )),
      v1_Attributes), type table),
    v1_RemovedOtherColumns = Table.SelectColumns(v1_Ad_Table,{"Name", "t"}),
    #"v1_Expanded Custom" = Table.ExpandTableColumn(v1_RemovedOtherColumns, "t", v1_Attributes),
    StepBack = Source,
    v2_Attributes = List.Buffer(List.Distinct(StepBack[Attribute])),
    v2_StepBack = Source,
    v2_TransformMany = List.TransformMany(
      Table.Split(v2_StepBack, List.Count(v2_Attributes)),
      each {Table.PromoteHeaders(Table.FromRows(List.Skip(Table.ToColumns(_))))},
      (x,y)=> Table.AddColumn(Table.FromRecords({x{0}[[Name]]}), "Data", each y)
),
    v2_CombinedTables = Table.Combine(v2_TransformMany),
    v2_ExpandedData = Table.ExpandTableColumn(v2_CombinedTables, "Data", v2_Attributes)
in
    v2_ExpandedData

[dufoq3 · Accepted solution]

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"

[tackytechtom]

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/