topic Re: Displaying columns as a grid? in Custom Visuals Development Discussion

Displaying columns as a grid?

Anonymous — Wed, 19 May 2021 17:55:32 GMT

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

Is it possible?

Thanks!

Re: Displaying columns as a grid?

lbendlin — Tue, 25 May 2021 02:44:35 GMT

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.

Re: Displaying columns as a grid?

Anonymous — Fri, 28 May 2021 12:56:28 GMT

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

Re: Displaying columns as a grid?

lbendlin — Fri, 28 May 2021 13:00:34 GMT

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

Re: Displaying columns as a grid?

Anonymous — Fri, 28 May 2021 14:20:38 GMT

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.

Re: Displaying columns as a grid?

lbendlin — Fri, 28 May 2021 15:18:57 GMT

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"

Re: Displaying columns as a grid?

Anonymous — Tue, 01 Jun 2021 09:31:48 GMT

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

Re: Displaying columns as a grid?

lbendlin — Tue, 01 Jun 2021 10:43:06 GMT

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?

Re: Displaying columns as a grid?

Anonymous — Tue, 01 Jun 2021 13:19:13 GMT

Ok Thanks! I'll try it 🙂