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

vangiang's avatar
vangiang
Regular Visitor
1 year ago

hi All. i noticed the icon in front of that Query 2 looks very thin, wonder if that indicates distinct values? Thanks in advance.

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi vangiang , not sure if this is still helpful, but that means that your query is in List form. The implication is that most of the operations you can apply to the data, like Merge, Append, Replace values and what-not are not available.

To hijack the response given above, you can convert it to a table using the following:

let
    DistinctSource = Table.FromList(List.Distinct(Sheet2[Date]))
in
    DistinctSource