Forum Discussion

CSUbux8's avatar
CSUbux8
New Member
6 years ago

Group data by column headers in data source

I am trying to create groups using coumn headers if possible.   I have a single row of data points for each CustomerID with many column headers. I would like to create some "buckets" or groups of p...
Need Help
dax's avatar
dax
Community Support
6 years ago

Hi CSUbux8 ,

You could try to unpivot column , then add custom column to group data, then you could get the group, and you could put this column in "Column field" of Matrix.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0AhJGxkAChM2VYnWilZJAPBDX0BAkCSLMDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, p1 = _t, p2 = _t, p3 = _t, p4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"p1", Int64.Type}, {"p2", Int64.Type}, {"p3", Int64.Type}, {"p4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"name"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute] ="p1" or [Attribute] ="p2" then "g1" else "g2")
in
    #"Added Custom"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.