Forum Discussion

FrisoW's avatar
FrisoW
Icon for Helper I rankHelper I
5 years ago
Solved

Empty colums

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 co...
Need Help
  • FrisoW's avatar
    FrisoW
    5 years ago

    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.

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
5 years ago

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

FrisoW's avatar
FrisoW
Icon for Helper I rankHelper I
5 years ago

Hi Pragati,

 

Thanks for your reply. The values can contain decimals.

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

 

Thanks

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      FrisoW
      Icon for Helper I rankHelper I
      5 years ago

      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's avatar
        Pragati11
        Icon for Super User rankSuper User
        5 years ago

        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