Forum Discussion
Re-structuring a table
Hi Anonymous
I cant uplad the PBIX file, but here I tried an example with a manual input table. I share with you my power query transformations, you should be able to replicate it in a blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzDUNzJU0lFyTiwBkv55qUqxOigSQanFQDLUMQRdAqIjpDwfuw5XvzAcOjKKUnHYEuIaDLQmFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Column1", type date}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column2]), "Column2", "Column3", List.Min), #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Cat"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Res] <> null)) in #"Filtered Rows"
Anonymous , Unpivot the table, use MAX as aggregation
amitchandak the table is already unpivoted, i'm not sure how to unpivot it again, to get my desired result. I tried a few things but nothing returns what I want.
The other option from Anonymous is a possibility but I think I should be able to re-model the data within the same table somehow. I got this far, I don't want to give up!
Hi Anonymous
I cant uplad the PBIX file, but here I tried an example with a manual input table. I share with you my power query transformations, you should be able to replicate it in a blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzDUNzJU0lFyTiwBkv55qUqxOigSQanFQDLUMQRdAqIjpDwfuw5XvzAcOjKKUnHYEuIaDLQmFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Column1", type date}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column2]), "Column2", "Column3", List.Min), #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Cat"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Res] <> null)) in #"Filtered Rows"