cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 new table with those values, so I can use it as a dimension and for forming relationships in my model.

 

Right now, I have referenced the first query, deleted all the columns besides the one I need, and then done a "Remove Duplicates", with a result of two distinct text values. This works, but at query refresh, this simple table containing only two text values as a final result will load all 1,5 million rows first, and then clean it up.

 

Is there a more efficient way to complete the same task in Power Query, so I can reduce refresh time?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Power Query: Extract distinct values from column as new query

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)

9.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

7 REPLIES 7
Community Support Team
Community Support Team

Re: Power Query: Extract distinct values from column as new query

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)

9.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

BiBra Member
Member

Re: Power Query: Extract distinct values from column as new query

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?

Community Support Team
Community Support Team

Re: Power Query: Extract distinct values from column as new query

Hi @BiBra,

 

Nope, it just means the description of my formula.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
BiBra Member
Member

Re: Power Query: Extract distinct values from column as new query

@v-shex-msft Okay, however the code does not work for me

BiBra Member
Member

Re: Power Query: Extract distinct values from column as new query

I get this:

fault2.PNG

Highlighted
chahatkaur Regular Visitor
Regular Visitor

Re: Power Query: Extract distinct values from column as new query

Hello

I faced the same problem you asked for and i am quite new to power bi and mostly know its basics.

what i did was i created a new table in power bi desktop and  used the formula

 

NewTable = DISTINCT(Tablename [field])
 
and it worked.

Re: Power Query: Extract distinct values from column as new query

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"

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 217 members 2,196 guests
Please welcome our newest community members: