Forum Discussion
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...
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 DistinctSourceSheet2(22582 rows) -> Date(953 rows)
Regards,
Xiaoxin Sheng
In the Power Query editor:
Right click on your existing query and choose "Reference".
Use "Choose Columns" to select only the column which you want your unique values generated from.
Right click on the column and choose "Remove Duplicates"
This is the users current solution, however this is inefficient as it loads the entire table and then performs the transformations.
Current best solution is:
let
DistinctSource = List.Distinct(Sheet2[Date])// List.Distinct(QueryName[ColumnName])
in
DistinctSourceThanks! This is just the alternative I was looking for.
Your code is slightly wrong. I've edited it below 🙂
let DistinctSource = List.Distinct(Sheet2,"Date")// List.Distinct(QueryName,"ColumnName") in DistinctSource
