Can I exclude columns from visual only if blank?

[chris886]

I'm trying to drill through from a main visual that has numerous columns. Each row has many (but always different) columns with no data. I'd like if the resulting visual (which is a single row) could eliminate those columns so it doesn't show a bunch of blanks between actual values.

Is this possible? Can anyone help start me down the right path for figuring it out?

[v-eqin-msft · Accepted solution]

Hi @chris886 ,

You may firstly unpivot the table and filter out the [Value] ="" in Power Query:

Below is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRikwtBpJBqSlA0imnNBVIuRelpuYBab98IKGAhCFqgwuKMvPSgQy3xJwcsEysTrSSV2YuNi04zAjPzCtJLQIZVpqbC2aADQMblJ+RB1cHc4p/UWJeeipYOCcnvxwuH1oAJFzyy0FqfFLTSjBtjI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"1" = _t, #"1A" = _t, #"1B" = _t, #"1C" = _t, #"2" = _t, #"2A" = _t, #"2B" = _t, #"2C" = _t, #"3" = _t, #"3A" = _t, #"3B" = _t, #"3C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"1", type text}, {"1A", type text}, {"1B", type text}, {"1C", type text}, {"2", type text}, {"2A", type text}, {"2B", type text}, {"2C", type text}, {"3", type text}, {"3A", type text}, {"3B", type text}, {"3C", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " "))
in
    #"Filtered Rows"

 After apply the changes, you could create a matrix visual:

If you want to remove the blank values, please create a slicer visual to select a specify Name, in default, the matrix visual will remove blank values in default, or you could apply basic filters as well:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chris886]

Thank you! Sorry didn't see your reply until now.

[VijayP]

@chris886 

When you are visusalising, the Context is Row-wise not by column-wise, So eliminating only one column is not possible once you have added that to Columns because in a specific Row other columns also there which are carrying information. However if you can share some sample data, I can see what else can be done!

[chris886]

Edit: It keeps destroying my tables when I post. Not sure why.

My dataset is currently a single table with results of a form that has dynamic/logic questions. So if they answer 'No' to a question, they do not get the follow up questions. Here's an example.

Name

1

1A

1B

1C

2

2A

2B

2C

3

3A

3B

3C

Bob

Yes

Red

Blue

Green

No

Yes

Spring

Fall

Jim

No

No

Yes

Winter

Summer

Fall

John

Yes

Green

Orange

Yellow

Yes

Up

Down

Left

No

I'd like to drill through on the Name for example, and get a single result showing the questions they've answered. I know how to show columns as rows in a Matrix, and so far I can get to the following:

Name	Bob
1	Yes
1A	Red
1B	Blue
1C	Green
2	No
2A
2B
2C
3	Yes
3A	Spring
3B	Fall
3C

Is there a way to have the visual exclude questions 2A - 2C, and 3C for this result?

I'm still a beginner myself, but I do have a BI Analyst resource available to help if the answer is complicated. Was just trying to see if I could figure it out.

Thank you.