Forum Discussion

Alirezam's avatar
Alirezam
Icon for Helper V rankHelper V
5 years ago
Solved

column statistics showing wrong result for distinct values

Here we go, this is very annoying when you have a column having about 40 distinct values but you will see only 18, and even after you click on 'load more' from the drop-down arrow, nothing will change. I can count by my eyes and see there are 40 distinct values. Find an attached photo. Any indication or suggestion? This cannot be that stupid!

Need Help
Show and Tell
  • selimovd's avatar
    selimovd
    5 years ago

    Hey Alirezam ,

     

    by default Power Query is only loading and analyzing the first 1000 rows.

    That's also the reason you see above "Count 1000".

     

    Change the column profiling In the bottom of the window to the entire data set:

     

    Then you should see the real amount of distinct values.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

2 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey Alirezam ,

     

    by default Power Query is only loading and analyzing the first 1000 rows.

    That's also the reason you see above "Count 1000".

     

    Change the column profiling In the bottom of the window to the entire data set:

     

    Then you should see the real amount of distinct values.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Alirezam's avatar
      Alirezam
      Icon for Helper V rankHelper V
      5 years ago

      Thanks indeed 🙂

      To be honest I could not find it by myself. It's in a very wrong place, not user-friendly!