Re-structuring a table

[Anonymous]

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

 

[Anonymous]

Thanks @RubenHd this worked - I had to manually code the pivot instruction because Power Query wouldn't seem to give me the solution you had. This is really great!

[HotChilli]

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.

[amitchandak]

@Anonymous , Unpivot the table, use MAX as aggregation

https://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous]

@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!

[Anonymous · Accepted solution]

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"