Forum Discussion
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 would work. And if we can get in percentage.
Thank you so much! Let me know if i can explain more.
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
9 Replies
Anonymous , You can try like for a column
divide(calculate(countrows(Table), filter(Table, isblank(Table[Question1]))), countrows(Table))
or
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Anonymous , I think that it might need unpivoting , summing up 40 would be challenging
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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
Hi Anonymous
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
Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a parameter as below.
You may add a new step with the following m codes.
= Table.AddColumn(#"Changed Type", "Not Null Percentage", each List.Count( List.Select(List.Skip(Record.ToList(_),1+Parameter1),each _<>""))/ List.Count( List.Skip(Record.ToList(_),1+Parameter1)))Finally you may modify the parameter to skip n columns to get the final result. When the parameter is 3, here is the result(Only A4 and A5 count).
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
