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"
Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi Anonymous,

 

How about this

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Names", type text}, {"Values", type any}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Names]), "Names", "Values")
in
    #"Pivoted Column"

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks Ashish_Mathur Marcel beat you to it. I know have a better understanding of the Pivot function. I've used it all the time when crunching data, but have always leveraged that last parameter in the function.

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    Actually in the pivot popup you can choose "Advanced options" and then you can choose "Don't Aggregate".