Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

     

     

    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"

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

 

 

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"

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous , many thanks. It worked like magic!