Forum Discussion
Re-structuring a table
I have a ghastly column of tags.
The tags contain a category, and a value for that category. E.g Category = Environment, Result = UAT
There are up to 32 tags in the single column. I've managed, with a great deal of modelling, to organise the 32 tags into two columns.
The first column [Attribute] contains whether [Value] is the Category (Cat) or the Result
The second column [Value] is the Category value, or the Result value
I have a KEY column so I can create a relationship between the core table of data, and the associated tags
Now I want to force the Attribute column into two columns: [Cat] and [Result] and my [Value] column, to hold the associated value - thus halving the number of rows in my table
Attached are models of my current table, and my desired result. Please tell me how to go about this, if possible!
BEFORE
AFTER
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"
5 Replies
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"
The classic method is to add a column that identifies the related pairs of rows before doing the pivot, as per
https://community.powerbi.com/t5/Desktop/Using-Power-Query-Advanced-Editor/m-p/2304845
As always, please post text versions of the data, not pictures, for more detailed and quicker help.
