cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
J-Saw Frequent Visitor
Frequent Visitor

Power Query: Generate Queries based on Distinct Values.

Good Morning, Community:

I have this table, which contains demand for specific items, on a given location and a given date:

DateProduct ID Location IDDemand (Qty)
01-01-2018a34
01-02-2018b130
01-03-2018c47
01-04-2018e310
01-05-2018d317
01-06-2018a319
01-07-2018b144
01-08-2018c323


Assuming the demand is already grouped (Sum) by Date, Product ID and Location ID, is there a way of generating several queries based distinct Product IDs and Location IDs, containing their respective demand?

This how it would look like for product "a" on location "3":

DateProduct ID Location IDDemand (Qty)
01-01-2018a34
01-06-2018a319


Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Power Query: Generate Queries based on Distinct Values.

Hi @J-Saw,

 

This is easy to do with DAX. But if you'd like the Power Query solution, please refer to the following steps.

1. Create a function.

(ProductID as text, LocationID as number) as table=>

let
    result = Table.SelectRows(Table1, each [Product ID] = ProductID and [Location ID] = LocationID)
in
    result

2. Invoke the function with parameters.

Power-Query-Generate-Queries-based-on-Distinct-Values

Please download the demo in the attachment. 

 

Best Regards,
Dale

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

Re: Power Query: Generate Queries based on Distinct Values.

Hi @J-Saw,

 

This is easy to do with DAX. But if you'd like the Power Query solution, please refer to the following steps.

1. Create a function.

(ProductID as text, LocationID as number) as table=>

let
    result = Table.SelectRows(Table1, each [Product ID] = ProductID and [Location ID] = LocationID)
in
    result

2. Invoke the function with parameters.

Power-Query-Generate-Queries-based-on-Distinct-Values

Please download the demo in the attachment. 

 

Best Regards,
Dale

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