Power Query errors

[Neok]

Hi all,

I'm trying to put in a dataset but im getting errors. I optimalized my dataset to put in all the empty fields an '-'. 
The following error is coming up. It has to do something with the datatype. There are all numbers in the fields, except for the empty fields which i replaced with '-'. So PowerBi Query doesn't reconize the fields. How to fix this?
For example:

Thanks in advance!

[BA_Pete · Accepted solution]

You're better off removing all the '-' from the source. Power Query works only on raw data and data types, it doesn't really understand presentation formatting like this.

If you really want to display '-' instead of blanks in reporting (it's not a standard thing to do) you can paste a new format pattern in here, using the same pattern structure that Excel format patterns use:

Pete

[Neok]

@BA_Pete Thanks a lot it worked 🙂

Next question is the following:

I got fields with a lot of empty cells, its marked as '(Leeg)'. How can i show them in my graphic?

[BA_Pete · Accepted solution]

This thread should help you to show blank counts:

https://community.powerbi.com/t5/Desktop/Include-count-of-blanks-in-a-chart/td-p/1648482 

Pete

[Neok]

@BA_Pete Thanks for your comment.

The problem I had was as following. My dataset wasnt consistent. For example I had the following cells (look at column AK):

So sometimes it gives a value, and sometimes an empty cell is 'empty', sometimes an empty cell is an '-'. I tried to import it at PowerBi and it was also giving errors. So that was the reason I decided to replace all 'empty cells' in my Excel to an '-'.

I also want to show empty cells in my powerbi as a graphic. For example: Column x has a filling degree of 20%. I thought it would be easiest to mark these with '-'

[BA_Pete · Accepted solution]

You're better off removing all the '-' from the source. Power Query works only on raw data and data types, it doesn't really understand presentation formatting like this.

If you really want to display '-' instead of blanks in reporting (it's not a standard thing to do) you can paste a new format pattern in here, using the same pattern structure that Excel format patterns use:

Pete

[BA_Pete]

Hi @Neok ,

Adding '-' into empty cells in Power Query isn't an optimisation by any definition.

The reason you're getting these errors is because PQ is looking for specific data types (the ones you set on the column) to apply to every value in the column. '-' is not a number, nor is a date, therefore PQ can't apply that data type to it, therefore you get an error for those values.

Remove whatever steps you implemented in order to place them in there and that will fix your errors.

If you want blanks to show as '-' in your reports, this will need to be done with either custom format in Excel or a custom measure in Power BI.

Pete