Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fstupot
Helper II
Helper II

how report questionnaire answers as 0

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

 

cheerscolumn exampole.JPG

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

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

v-eqin-msft
Community Support
Community Support

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"

3.2.1.unpivot.PNG

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
Helper II
Helper II

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

@fstupot 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@fstupot 

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

1.PNG2.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.