Displaying columns as a grid?

[Anonymous]

I have data in the above format and I wish to show it like this for any particular day:

Is it possible?

Thanks!

[lbendlin]

yes, this is certainly possible in Power Query or in DAX  (setting aside the question of why?!? )

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[Anonymous]

I can't seem to upload an excel here - it isn't too difficult to re-create though.

[lbendlin]

you can include text tables in your post. No need for Excel.

[Anonymous]

I tried many times and it gives me an "HTML detected" error. When clicking on "remove HTML and try again" it goes into a loop. Which is why I had to paste it as a picture to begin with.

[lbendlin]

Here is a simplistic approach using some Power Query but mostly the matrix visual.  Let me know if you need it all in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JY3BCsIwDIbfpefB1j6CO8yDguhA2Ogh1mwNurbE7v1N9JL/40/4Ms/GtrZ1nbOmMSfKSWIghmVBoT4iVohCV3zKPLx3rQdG1MMemHMVGPMGNQtcsBTkj/GNip2KOzUfqRTdj7QiS074YJA8w4qpKt0jVfy9gPBSU65/5bSHECmR4K1QghCN918=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Animal 1" = _t, #"Animal 2" = _t, #"Animal 3" = _t, #"Colour 1" = _t, #"Colour 2" = _t, #"Colour 3" = _t, #"Veg 1" = _t, #"Veg 2" = _t, #"Veg 3" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"})
in
    #"Split Column by Delimiter"

[Anonymous]

Great! That seems to work in the answer, but I still need to understand how to put that in Power BI (DAX?)

[lbendlin · Accepted solution]

There's not really a need to do that in DAX. All the necessary transforms are done in Power Query. Or am I missing something?

[Anonymous]

Ok Thanks! I'll try it 🙂