Formatting of values, which need to be excluded

[Anonymous]

I got following use case:

We´ve made a survey, to collect corporate environment data. This survey consists basically of 15 questions, where the answers reach from (1=very poor to 5= very good). We now got the situation, that a bunch a survey participants forgot to answer all questions. If there´s no answer for one of the questions, we used a "0" value in data processing.

So basically my data model in the PowerQuery editor looks like this example:

[n] is the index of the question, and the value describe the given answers.

Later in the visuals, i want all answers to a question to be excluded, if the value = "0", because otherwise it would distort the results. -> If i use any MEAN oder SUM function in the visualizations, the 0 would be included by default.

How can i exclude these values? Formatting them as "0" was necessary to set the column type to "number".

Before formatting the value of all 0 was "no answer!".

I can´t just remove the whole row, because in most cases the other 14 questions are answered and need to be included in the data model.

[Anonymous]

Edit: It seems, that the replacement of 0 with "null" solved the issue!

[dharmendars007 · Accepted solution]

Hello @Anonymous , 

You can either replace the 0 with null values by selecting the entire table or select the entire table and Unpivot the same and filter out 0

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN