Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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".
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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |