Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
It's baffling how much time I've spent trying to solve this:
What I simply have is:
TAG | VALUE1 | VALUE2 |
BOB001 | A | 4 |
BOB002 | F | |
BOB003 | 7 |
What I simply want is a count from the whole table like COUNTA(Table) = 7
Defining columns by name in the formula is not an option because I have 30 tables with continuously changing column names, I know; exciting stuff 😞
This is eventually used to calculate how complete a table is, using the table above as an example; (7/9)x100
I'm looking for a solution in either Power BI or Power Query
Solved! Go to Solution.
I have a feeling you can, so I did some research - the solution turned out to be pretty simple.
You have to do some hacking in the Power Query "Advanced Editor".
Let's say the original table (with 3 columns) is called "T1" in the M script, then you can add two more lines to add the two calculated columns you wanted:
let
...
, T2 = Table.AddColumn(T1, "COUNT ACTUAL", each List.NonNullCount( Record.FieldValues(_) ) )
, T3 = Table.AddColumn(T2, "COUNT EXPECTED", each -1 + List.Count( Record.FieldValues(_) ) )
in
T3
If you unpivot all the columns (in Power Query), you will obtain a single column that contains all the table cells. Then, it's a simple matter of counting which ones are non-blank.
Do you know a trick I can use to do this for example: (Adding Columns)
TAG | VALUE1 | VALUE2 | COUNT ACTUAL | COUNT EXPECTED |
BOB001 | A | 4 | 3 | 3 |
BOB002 | F | 2 | 3 | |
BOB003 | 7 | 2 | 3 |
I'd love to keep the table for re-use.
If not, using this query as a source for a new query (unpivot) might be the way to go. And I'll accept the answer.
I have a feeling you can, so I did some research - the solution turned out to be pretty simple.
You have to do some hacking in the Power Query "Advanced Editor".
Let's say the original table (with 3 columns) is called "T1" in the M script, then you can add two more lines to add the two calculated columns you wanted:
let
...
, T2 = Table.AddColumn(T1, "COUNT ACTUAL", each List.NonNullCount( Record.FieldValues(_) ) )
, T3 = Table.AddColumn(T2, "COUNT EXPECTED", each -1 + List.Count( Record.FieldValues(_) ) )
in
T3
You, sir, ARE A GENIUS!
What I have in place of nulls are blanks; I think it'll be easy to resolve this last part. If you have the answer, please add it on top so it may benefit others. Kudos to you.
That's the one amendment (to search for blanks rather than nulls):
COUNTACTUAL = Table.AddColumn(#"Removed Columns", "COUNT ACTUAL", each + List.Count(List.Select(Record.FieldValues(_),each _ <> ""))),
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |