Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Power Query: Extract distinct values from column as new query

Hi,   I have a fairly large query with 1,5 million rows, which is growing by around 5000 rows per day. From that query I want to extract the distinct values from one specific column and create a ne...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    You can try to create a blank query to reference original source, then use list.distinct to remove duplicate records.

     

    let
    DistinctSource = List.Distinct(Sheet2[Date])// List.Distinct(QueryName[ColumnName])
in
    DistinctSource

     

    Sheet2(22582 rows) -> Date(953 rows)

     

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Anonymous,

 

You can try to create a blank query to reference original source, then use list.distinct to remove duplicate records.

 

let
    DistinctSource = List.Distinct(Sheet2[Date])// List.Distinct(QueryName[ColumnName])
in
    DistinctSource

 

Sheet2(22582 rows) -> Date(953 rows)

 

 

Regards,

Xiaoxin Sheng

  • BiBra's avatar
    BiBra
    Helper III
    8 years ago

    I have a question for this. I am trying to do this myself:
    you write // List.Distinct(QueryName[ColumnName]), is this the new query you're working in?

    If not, what is it?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi BiBra,

       

      Nope, it just means the description of my formula.

       

      Regards,

      Xiaoxin Sheng

      • BiBra's avatar
        BiBra
        Helper III
        8 years ago

        Anonymous Okay, however the code does not work for me

  • NotHappyAtAll's avatar
    NotHappyAtAll
    Frequent Visitor
    2 years ago

    This works. But the query still refreshes all the 1.5 million records! So it does not solve the question.

    That means you still have to wait for the query to finish once more. This is made like this to "make shure" Power-Bi have all the data once more pulled from the souse. Quite annoying actually.    

  • nsampath's avatar
    nsampath
    Regular Visitor
    1 year ago

    Thanks for posting the solution, it definitely helped me tackle the problem that I was facing.