Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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"
Regards
Phil
Proud to be a Super User!
Hi, @chrisdavila
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.
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"
Regards
Phil
Proud to be a Super User!
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
Proud to be a Super User!
Hi @chrisdavila
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
Proud to be a Super User!
@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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |