Level of data completeness – how many Null values do we have?

[Anonymous]

Hello everyone,

I am new to Power BI and I love the program! I'm stuck with something and I hope you can help me.

I'm working for an engineering company that is the prime contractor. We are evaluating data we receive from the engineering service providers (subcontractors). We create data reports every month.

As the project progresses, the data mature, which means that currently some values are missing but will be added in the future. What we want is to track the maturing of the data. Basically, we want to show the level of data completeness once every month. Please take a look at this table. How many Null values do we have and how can we compare them to the rest of the data? The goal is to create a visualization that shows something like: 30% of the whole data are null values, or data is 70% complete.

I hope you can help us and thanks,

Andrej Ilić

BIM engineer

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I suggest you create a profile table in pq to get the nullcount and count, then create two measures in desktop to display the results.

Like this:

= Table.Profile(tablename)

complete = DIVIDE(SUM(Query1[Count])-SUM(Query1[NullCount]),SUM(Query1[Count]))

nullvalue = DIVIDE(SUM(Query1[NullCount]),SUM(Query1[Count]))

Reference:Table.Profile - PowerQuery M | Microsoft Docs

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[jppv20]

Hi @Anonymous ,

I don't know if there is a standard way for doing this.

I assume that there is at least one column where each row is filled with data? In that case a solution might be to do a count on that column. The total of this column x the number of columns should be the number when your data would be complete. Then you create a count for each different column (null values are not counted) and sum these totals. The total count of the different columns divided by the number if the data would be complete should give you the percentage of complete data.

Hope this helps.

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.