cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisdavila
Frequent Visitor

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. 

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

Hi @chrisdavila 

 

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"

percnull.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

9 REPLIES 9
v-alq-msft
Community Support
Community Support

Hi, @chrisdavila 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

d1.png

 

You may create a parameter as below.

d2.png

 

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).

d3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

PhilipTreacy
Super User III
Super User III

Hi @chrisdavila 

 

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"

percnull.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

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!

 

 

Not sure how to add this to my data set - do i just append it to my data. let me attach all my collumn headers so you can see :

 

Title
Network
Agency
Location
Assessment Type
Submitting Colleague
Network Contact
Agency CEO
Network CEO
Network CFO
Define Current Business
Current Agency Structure
Current Core Agency Function
Current Department Count
Current Project Manager
Future Agency Structure
Approval
Current Capabilities
Future Capabilities
Current Clients
Agency Colleague Count
Current Colleagues Onsite
Current Colleagues Flexible Work
Current Colleagues Offsite
Current Sr Level Colleagues
Current Mid Level Colleagues
Current Jr Level Colleagues
Future Agency Structure Redefined
Redefined Roles Explanation
Roles For Consolidation Count
Planned RIF Timeline
Future Agency Colleague Count
Current Additional Workspace
Future Seating Count
Future Fixed Seats
Future Agile Seats
Current PM Application
Current PM Application Name
Describe Business Growth
Agency Common Workflows
Agency Workflow Gaps
Agency Workflow Impact
Future Plans Timeline
Total Onsite All
Total Hybrid All
Total Offsite All
Onsite Classification1
Onsite Classification2
Onsite Classification3
Hybrid Classification1
Hybrid Classification2
Offsite Classification1
Offsite Classification2
Offsite Classification3
Colleagues at Unassigned Locations
Freelance Count
Current Workspace Challenges
Current At Max Workspace
More Meeting Rooms
More Phone Booths
More Huddle Spaces
More Cafes
Future Specialist Workspace
Future Library Space
Opportunities
Current Agile Seating
RejectionComment
More Library Space
Noise Challenges
Interruption Challenges
Meeting Room Tech
WiFi Connectivity
Future Workspace Utilization
Meeting Rooms Variety
Quiet Space Challenges
Huddle Space Challenges
Social Space Challenges
Specialist Space Described
Enhanced Meeting Tech
Enhanced Tech Described
Storage Requirements
Space Needs
Estimate Considerations
Fixed Seats Rationale
Link to result files
PlanDescription
Describe Workspace
Key Workspace Issues
Facilities IT Support

 

Last question - can i start the count of collumns at collumn 11 since 1-10 will be prefilled?

Hi @chrisdavila 

If you could post your entire query that would make it easier for me to integrate my code with it but let's give it a go.

Not sure what you mean by your first 10 cols are pre-filled, but it's no bother to discount those from calculations so that only columsn 11 onwards are included.

In your query you will have a Source step and then maybe some other transformation steps.  My code needs to be inserted after these.  If you only have a Source step then even easier.

What you must do though is edit the name of the previous step in my first line of code.  In my first line I've chnaged it to xxxxxxxxx, so if the line of code in your query immediately before mine is the Source step then change xxxxxxx to Source

    #"Added Index" = Table.AddIndexColumn(xxxxxxxxx, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Non Null", each List.NonNullCount(List.RemoveFirstN(List.RemoveLastN(Record.ToList(#"Added Index"{[Index]}),1),10))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Num of Cols", each List.Count(Record.ToList(#"Added Custom"{[Index]}))-12),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Percentage Not Null", each [Non Null] / [Num of Cols]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Percentage Not Null", Percentage.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index"})
in
    #"Removed Columns"

If you can't get it to work I'l need to see your entire query.  I don't need the data, just copy/paste the query in here.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi @chrisdavila 

 

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

percnull2.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


amitchandak
Super User IV
Super User IV

@chrisdavila , 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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Not sure if this will work. 

 

Every question is a column in my list so basically if i have 40 columns and 20 of them are not blank then i have completed 20/40 questions. 

@chrisdavila , 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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors