Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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, wi...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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!