Forum Discussion
Counta for Completion Status
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
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
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!
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
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 SupportLast question - can i start the count of collumns at collumn 11 since 1-10 will be prefilled?
Hi Anonymous
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