Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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.
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
could you please provide what you get now and what's the expected output?
Proud to be a Super User!
have you tried to select all the other columns and unpivot other columns in PQ?
Proud to be a Super User!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |