Forum Discussion

bryantw's avatar
bryantw
New Member
2 years ago
Solved

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....
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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"
bryantw's avatar
bryantw
New Member
2 years ago

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's avatar
dufoq3
Community Champion
2 years ago

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