Visualize in stacked bar chart the missing percentage of every column.

[Iohanney]

Hello!

I would like to show on one stacked bar chart the missing values (null) percentage of every column for data quality purposes.

1. Tried to Unpivot the table and calculate the blank and the total for the attributes column, but it doesn't work.

2. Than tried to transpose the table and count the blank values per row: List.Count(List.Select(Record.FieldValues(_), each _ = "" or _ = null)), but it gives an error (The syntax for 'Count' is incorrect. (DAX(List.Count(List.Select(Record.FieldValues(_), each _ = "" or _ = null)))).

Thanks in advance

[v-veshwara-msft]

Hi @Iohanney ,

We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.

If you still need assistance, please let us know.
Thank you.

[v-veshwara-msft]

Hi @Iohanney ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @Iohanney ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you

[v-veshwara-msft · Accepted solution]

Hi @Iohanney ,
Thanks for posting in Microsoft Fabric Community,

To visualize the missing/null percentage for each column, please refer to the steps below.

I created a sample dataset and reproduced the expected result

1. Created sample data manually using "Enter Data" in Power BI with columns like ID, Name, Age, City, and Score.
   Some values were left blank to simulate missing data.

   

2. Demoted headers and Transposed the table:

   

3. Added a column to count null or blank values:
   List.Count(List.Select(Record.ToList(_), each _ = null or _ = ""))

   
4. Added a column to count total values:
   List.Count(Record.ToList(_))
   
5. Added a column to calculate the null percentage:
   [NullCount] * 100 / [TotalCount]
   

6. Created a stacked bar chart:

   Y-Axis: Column1

   X-Axis: NullPercent

   

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.

[bhanu_gautam]

@Iohanney Go to the "Transform Data" option in Power BI to open Power Query Editor.
Select your table and add a new column for each existing column to calculate the null count. You can use the following formula in Power Query to calculate the number of nulls in each column:

Table.AddColumn(#"PreviousStepName", "NullCount_ColumnName", each List.Count(List.Select(Record.FieldValues(_), each _ = null)))

Add another column to calculate the total number of rows in the table. You can use:

Table.RowCount(#"PreviousStepName")

Add a custom column to calculate the percentage of nulls for each column:

Table.AddColumn(#"PreviousStepName", "NullPercentage_ColumnName", each [NullCount_ColumnName] / [TotalRows] * 100)

Once you have the null percentages calculated for each column, you can unpivot these columns to create a format suitable for a stacked bar chart.
Select the columns with null percentages and use the "Unpivot Columns" option.

Load the transformed data back into Power BI.
Create a stacked bar chart using the unpivoted data. Set the axis to the column names and the values to the null percentages.

[Iohanney]

@bhanu_gautam Thanks! The "PreviousStepName" is the table name and where should I put the column name in the first expression?