Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Denesting flat table with ID field

I have a sample dataset that mirrors a situation I have from my postgres database. The dev already flattened the JSON and now I want to transpose back into its respective columns. I have some sample ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    Something like this (I also adjusted your Source step):

     

    let
    Source = Table.FromRecords( 
             {
                 [id =123, names ="field_num",      values = "17"] , 
                 [id =123, names ="field_name",     values = "tagging"],
                 [id =123, names ="another_metric", values = "sales"],
                 [id =123, names ="custom_valu",    values = "CC"]
             }),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[names]), "names", "values")
in
    #"Pivoted Column"
MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

Something like this (I also adjusted your Source step):

 

let
    Source = Table.FromRecords( 
             {
                 [id =123, names ="field_num",      values = "17"] , 
                 [id =123, names ="field_name",     values = "tagging"],
                 [id =123, names ="another_metric", values = "sales"],
                 [id =123, names ="custom_valu",    values = "CC"]
             }),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[names]), "names", "values")
in
    #"Pivoted Column"
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Wow I feel kinda stupid, but I also didn't know you could remove the last parameter of the Table.Pivot (ususally its List.Sum() ) and get the results I wanted. Thanks for the help this past week. I've learned a lot.