how report questionnaire answers as 0

[fstupot]

hi guys,

i have imported some data where it is a long list of questions with the answers as 0,1,NA

there is about 40 plus questions which are the column headers. is there an easy way of reporting/visualising all the questions that have been scored as a 0.

i was going to use a tree map, but i dont know how to do it without adding 40+ filters to the visual

i know im being thick but really struggling

cheers

[Anonymous]

Hi @fstupot ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @fstupot ,

According to my understanding, you want to filter out the records when the value is 0 . 

As @ryan_mayu  said, you need to transform the original table by useing "unpivot", but this will affect other visualizations, you don’t want this, right?

So you could duplicate the table -->unpivot it -->Select rows=0:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNU0sFCxOpglzLAJmOIXw9UyhChEq89BlCZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Q1 Bed and" = _t, #"Q2 Suitable" = _t, #"Q3 Correct" = _t, #"Q4 Ffyrwefvj" = _t, #"Q5 Uhsdtde" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Q1 Bed and", type text}, {"Q2 Suitable", type text}, {"Q3 Correct", type text}, {"Q4 Ffyrwefvj", type text}, {"Q5 Uhsdtde", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "0"))
in
    #"Filtered Rows"

The disadvantage of this method is that it will take up more memory, resulting in slower running speed...

Please take a look at the pbix file here.

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.

[fstupot]

hi there,

when i do this  it creates a long list of the questions which is great......but the other visuals i have that report on total go through the roof

is there a way around this?

thanks so much for your help

[ryan_mayu]

@fstupot 

could you please provide what you get now and what's the expected output?

[ryan_mayu]

@fstupot 

have you tried to select all the other columns and unpivot other columns in PQ?