Forum Discussion

FrisoW's avatar
FrisoW
Helper 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
Super 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
    Helper 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
      Super 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
        Helper 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.

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

    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.