list of blank columns

[marwamahdhi]

Hello everyone ,

I'm trying to get a list of blank columns that i have in a table for each row.

i'll explain more

I have a source with 50 columns (project name, customer name, adress,...) and 168 rows and i want in the dashboard that every time i choose a project i'll have a list of empty columns for this project (which data is missing)

I tried to use IF fuction IF(isBlank([ColumnName]) ; ColumnName ; "" ) but as i have 50 columns it seems like it is impossible

any ideas please ?

Thank you

Have a nice day

[PattemManohar · Accepted solution]

@marwamahdhi You can achieve this in "Power Query Editor".. Here is the steps I've followed

Sample Data

Select all the columns apart from ProjectName and do "Unpivot only Selected Columns" then the output should like this way...

Unpivoted Result

As you are interested only in blank values, Filter only blank values from the value field. Then you are good to go with visualizing this data....

Final Output 

Hope this helps !!

[marwamahdhi]

Thank you @PattemManohar for your response ,  i followed your steps but the unpivoted result show me just the first 142 rows then an error pops up  and i didnt understand it 

[PattemManohar]

@marwamahdhi Is your Source is Sharepoint ? 

Can you please provide the sample data, to replicate your issue. There is some thing wrong. It should be more rows, (logically it should be no.of columns unpivoted x no.of rows) but you have only 142 rows.

[marwamahdhi]

Yes @PattemManohar my source is a Sharepoint

Yes i didn't know why it is showing me just the first project and some columns from the second

[PattemManohar]

@marwamahdhi While unpivoting, did you make sure you are selecting all the fields that you want to check NULL values but don't select the Project field... Also you need to select the option "Unpivot only Selected Columns" option

Apart from that, I can't see why you are facing an issue for unpivoting...

[marwamahdhi]

Hi @PattemManohar, thank you for your help, the issue in unpivoting was that i have a decimal type column and it could not be converted into a value so i fixed it and it works fine for me now.

Thank you  for your solution mentioned before. i hope this will help others too.

Have a nice day.