Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Counta for Completion Status

So i have a form that has lets say 40 questions. I want to track per row how many collumns have data in them and how many are null. Even if i just got the number of null out of total questions that w...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi Anonymous 

     

    Download PBIX with code and sample data.

     

    Here's a solution using Power Query.  This will work for any number of columns.  Open my sample PBIX to see how this works.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lFKSgISQJSSAiRSU5VidaKVYMLJyTAJsDCKeoRqsDBMNUwtXBHc3FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Non Null", each List.NonNullCount(List.RemoveLastN(Record.ToList(#"Added Index"{[Index]}),1))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Num of Cols", each List.Count(Record.ToList(#"Added Custom"{[Index]}))-2),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Percentage Null", each ([Num of Cols] - [Non Null]) / [Num of Cols]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Percentage Null", Percentage.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index"})
in
    #"Removed Columns"

    Regards

    Phil

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Now this is very very very close to what i want - two things -  i havd like 50-60 collumns do i have to manually type each one in the above formula? and instead of percent null - can i do percent not null!

 

 

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi Anonymous 

 

Download updated PBIX

 

It'll handle as many column as you like.  This bit grabs the current row and turns it into a list and it doesn't need to know how many columns exist before doing this

Record.ToList(#"Added Index"{[Index]})

 

and for the other bit, no problem, just change this

"Percentage Null", each ([Num of Cols] - [Non Null]) / [Num of Cols]

to this

"Percentage Not Null", each [Non Null] / [Num of Cols]

 which is done in the PBIX linked to above, givng this result

Regards

Phil