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"
tackytechtom's avatar
tackytechtom
Most Valuable Professional
2 years ago

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/