Empty colums

[FrisoW]

I have a similar issue (bug) I had before:

https://community.powerbi.com/t5/Desktop/Empty-columns/m-p/999467#M474749

The problem is more or less the same:

In "transform data" I have a column containing data and null values.

When I "close & apply", that same column is completely empty in the report and data view.

Back in "transform data" to troubleshoot the issue: when I create a filter to remove empty values, it removes everything.

PowerBI seems to think that column is empty, however you clearly see values (gif below).

This used to work perfectly for years, but the problem suddenly appeared. Other columns set up the exact same way still work as intended.

Any suggestion for a fix is welcome.

[Pragati11]

Hi @FrisoW ,

Unfortunately, I couldn't replicate this issue at my end with NULL values in a column.

I have one thought about the datatype of these columns. The column in your small video is of decimal type, but the few values I see in you column looks like whole number. Are they whole number type of values?

If yes, then before removing any empty values can you test one thing at your end please?

Rather than using Remove the Empty  option can you just uncheck null from your values and see how it goes?

Also, just one thing to add, Power Query Editor always shows few rows from the dataset not every other row.

Just try this and let me know how this goes.

Thanks,

Pragati

[HotChilli]

I agree with @Pragati11 with the 'filter out null' strategy.  Let's hypothesise that there are some 'weird' values in the column so it will be very much down to you to debug and investigate the data.

Choosing the 'Remove Empty' option will generate some M code so it would be good if you could show us that please.

While you do that, you might investigate the column quality with the options on the 'View' menu (distribution, profile, quality) and also change the column profiling option to be based on entire dataset (just click the info under the data in power query).

If you're not getting anywhere you might investigate by removing the changing of the datatype to decimal. set it to text and re-check column values.

Let us know how it goes. 

[FrisoW]

Hi Pragati,

Thanks for your reply. The values can contain decimals.

When I uncheck "null" I get the same error:

Thanks

[Pragati11]

Hi @FrisoW ,

In that case, I would say after the SOURCE step in Query editor where you have Changed Datatype step, modify this column's datatype to TEXT. Then try unchecking the NULL values.

Because this looks like a datatype issue to me.

The best thing would be to replcae these NULL values with 0 in your column, then try filtering out these values.

NULL values can sometimes create a lot of issues.

Thanks,

Pragati

[FrisoW]

Thanks for your input Pragati.

The problematic column is a conditional column with following config:

Previously the "else" value was "null". I changed it to "0".

This doesn't resolve the issue though, when I "remove empty" everything but "0" values are removed. Even when I create a filter "does not equal 123456789" all values except "0" disappear.

[Pragati11]

Hi @FrisoW ,

So this looks like a text column.

Can you replace 0 with NONE in your conditional rule?

Try to keep the values in same data format and then check.

If you still see issues, if it is possible share your pbix file by removing any sensitive information from it.

Thanks,

Pragati

[FrisoW]

The output ("Estimated value") is a decimal number, or 0 if "TO status" is not "TO".

So the conditional column is also a decimal number.

Even when I replace the 0 with "none", and I select "remove empty", all rows containing numbers are deleted except the ones containing "none". This makes completely no sense to me.

I'll try to remove confidential info of the pbix file later and upload it.

Thanks again

[FrisoW]

I found the problem but I cannot resolve it.

When I remove all columns that are not needed for that specific conditional column, the step to "remove empty" works like it should and I can filter how I want.

When I add those columns back (totally not related to those I use with the conditional column), the step fails.

I don't know how to proceed.

[FrisoW · Accepted solution]

I removed every column one by one so see when it went wrong.
It was the "opportunityid" column, data source is Dynamics CRM. Once I removed that column, every calculation worked like it should. It makes no sense, but hopefully this can help someone with the same issue.