Using single set of queries to achieve separate outputs

[KNalewajka]

I have a sales data set with a list of sites belonging to three different customers. Through series of queries I dynamically merge and split the columns, to have a dynamic list of columns for each site. It works fine for a single customer, but I need to make three separate outputs, each for separate customer.

Sample data:

Customer	Site	Product	Sales	Qty
Customer 1	Cust1_Site 1	Prod1	10	10
Customer 1	Cust1_Site 2	Prod2	15	15
Customer 1	Cust1_Site 1	Prod2	10	10
Customer 2	Cust2_Site 1	Prod1	15	10
Customer 2	Cust2_Site 2	Prod2	20	15
Customer 3	Cust3_Site 1	Prod1	25	30
Customer 3	Cust3_Site 2	Prod1	15	20

Output needed:

Customer 1:

Product	Cust1_Site 1 Sales	Cust1_Site 1 Qty	Cust1_Site 2 Sales	Cust1_Site 2 Qty
Prod1	10	10
Prod2	10	10	15	15

Customer 2:

Product	Cust2_Site 1 Sales	Cust2_Site 1 Qty	Cust2_Site 2 Sales	Cust2_Site 2 Qty
Prod1	15	10
Prod2			20	15

Customer 3:

Product	Cust3_Site 1 Sales	Cust3_Site 1 Qty	Cust3_Site 2 Sales	Cust3_Site 2 Qty
Prod1	25	30	15	20

Due to the fact that the list of sites changes from month to month I need to keep it as a series of queries and lists, which makes copying the whole process for each customer cumbersome.

I was told there might be a way to use customer name as a parameter and have Power Query use the whole process separately for each customer, by overriding the parameter from the output query. I can't figure out how to do it. Is that possible, and if yes, could anybody give me some hints as to how?

[mussaenda]

Hi @KNalewajka ,

it is advisable to do the separation in Visuals instead of in Power Query.

[v-sshirivolu]

Hi @KNalewajka  ,
Thank you @MarkLaf  for the helpful response!

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question

Thank you.

[v-sshirivolu]

Hi @KNalewajka 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you.

[v-sshirivolu]

Hi @KNalewajka !
May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[MarkLaf · Accepted solution]

Rather than changing the shape of your data in Power Query, this feels like something you can achieve more simply with a matrix and filters (eg use a single-select slicer).

If you need to ensure that certain users can only see one associated customer, then you can achieve with RLS: https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

If you have particular business requirements where the above is not applicable, then you could go the parameters route. Can you confirm that you are trying to do what is described here and, if yes, what is the part that you are getting stuck on? https://learn.microsoft.com/en-us/power-bi/connect-data/service-parameters