List of different products and average of quantity

[fabio7xavier]

Hello!

I have a big dataset that contains multiple purchase records of several customers in a store.

The table is composed with 7 columns: ClientID, Prod1, Prod1_Qt, Prod2, Prod2_Qt, Prod3, Prod3_Qt. 

In other words, for each client there is a record of 3 products purchased and the respective quantity.

Is there any way to obtain a table, with two different columns, the list of all products purchased and the average of the quantity?

Note that products are not always in the same order.

Client	Prod1	Prod1_Qt	Prod2	Prod2_Qt	Prod3	Prod3_Qt
1	clock	2	tracksuit	1	sneakers	3
2	ball	3	sneakers	2	racket	1
3	tracksuit	1	clock	2	ball	4

Thank you in advance!
-- Fábio Ferreira

[camargos88]

Hi @fabio7xavier ,

Are you brazilian ?

I've created this file as an example: Download PBIX 

I hope it helps,

Ricardo