TABLE TRANSFORMATION

[Jay29]

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

[Jay29]

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

[Anonymous · Accepted solution]

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"

[Jay29]

@Anonymous , many thanks. It worked like magic!