Denesting flat table with ID field

[Anonymous]

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 code, it should work, but feel like there is a more efficient way to do this.

let
 Source = Table.FromList( 
 {
 [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"]
 }, 
 Record.FieldValues, {"id", "names", "values"}),
 #"Added Custom" = Table.AddColumn(Source, "newcolumn", each Table.PromoteHeaders(Table.SelectColumns(Record.ToTable(Record.SelectFields(Source{_},{"names","values"})),{"Value"}))),
 #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"names", "values"}),
 #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
 #"Transposed Table" = Table.Transpose(#"Unpivoted Columns"),
 #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
 #"Removed Columns1" = Table.RemoveColumns(#"Promoted Headers",{"id_1", "id_3", "id_5"}),
 #"Expanded newcolumn" = Table.ExpandTableColumn(#"Removed Columns1", "newcolumn", {"field_num"}, {"field_num"}),
 #"Expanded newcolumn_2" = Table.ExpandTableColumn(#"Expanded newcolumn", "newcolumn_2", {"field_name"}, {"field_name"}),
 #"Expanded newcolumn_4" = Table.ExpandTableColumn(#"Expanded newcolumn_2", "newcolumn_4", {"another_metric"}, {"another_metric"}),
 #"Expanded newcolumn_6" = Table.ExpandTableColumn(#"Expanded newcolumn_4", "newcolumn_6", {"custom_valu"}, {"custom_valu"})
in
 #"Expanded newcolumn_6"

Initial state

Desired state

[Ashish_Mathur]

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]

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]

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

[MarcelBeug · Accepted solution]

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]

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.