Forum Discussion
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 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
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"
5 Replies
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"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"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.
Actually in the pivot popup you can choose "Advanced options" and then you can choose "Don't Aggregate".
