Count ALL Populated Cells in a Whole Table

[WALEED]

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

[DAX0110]

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.

[WALEED]

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.

[DAX0110 · Accepted 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

[WALEED]

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.

[WALEED]

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 _ <> ""))),