Forum Discussion
TABLE TRANSFORMATION
Hi everyone,
I am working on a table that looks like this in PowerBI:
| Type | Category | Date | Records | Party | Activity |
| MA-HP | HP-Auth | 6/1/2020 | 12345 | vendor | product |
| MA-HP | HP-Auth | 6/1/2020 | 556729 | vendor | sales |
| MA-HP | HP-Auth | 6/1/2020 | 23245 | client | product |
| MA-HP | HP-Auth | 6/1/2020 | 227823 | client | sales |
I want to transform (compress) it into the table below but can't seem to know how to go about it in PowerBI/PowerQuery. I need urgent help please.
| Type | Category | Date | Party | Product | Sales |
| MA-HP | HP-Auth | 6/1/2020 | vendor | 12345 | 556729 |
| MA-HP | HP-Auth | 6/1/2020 | client | 23245 | 227823 |
you should select the column Activity then transform with the pivot Column tool
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nVU0lHyCAASZvqG+kYGQIahkbEJkCpLzUvJLwIyCoryU0qTS5RidbAqNzUzt0BWXpyYk1qMS7GloYGhIZBOzslMzSshbLihsaEJsnKo4bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, Category = _t, Date = _t, Records = _t, Party = _t, Activity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Category", type text}, {"Date", type date}, {"Records", Int64.Type}, {"Party", type text}, {"Activity", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Activity]), "Activity", "Records", List.Sum) in #"Pivoted Column"
3 Replies
I guess the table was a little clumsy.
This is what I am working on:
Type Category Date Records Party Activity MA HP 6/1/20 1234 vendor product MA HP 6/1/20 5678 vendor sales MA HP 6/1/20 91011 client product MA HP 6/1/20 1314 client sales This is how I want it to look like (transformed/compressed)
Type Category Date Party Product Sales MA HP 6/1/20 vendor 1234 5678 MA HP 6/1/20 client 91011 1314 you should select the column Activity then transform with the pivot Column tool
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nVU0lHyCAASZvqG+kYGQIahkbEJkCpLzUvJLwIyCoryU0qTS5RidbAqNzUzt0BWXpyYk1qMS7GloYGhIZBOzslMzSshbLihsaEJsnKo4bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, Category = _t, Date = _t, Records = _t, Party = _t, Activity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Category", type text}, {"Date", type date}, {"Records", Int64.Type}, {"Party", type text}, {"Activity", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Activity]), "Activity", "Records", List.Sum) in #"Pivoted Column"