Show products purchased at a certain service

[FH_1]

Dear all,

I need you help with the following. I want to show what are the (top x) products I sold for a certain service. The service I sold has the same order_ID as the product. But I have a struggle to get this done and I hope I could explain it well enough.

My data table looks like this: 

2020-0001	2-1-2020 15:44	Dagmar	2676653	service	Heren knippen	1
2020-0002	2-1-2020 16:06	Dagmar	2679205	service	Heren knippen 2 week actie	1
2020-0003	2-1-2020 18:50	Dagmar	2709407	service	Woman | Cutting Delux	1
2020-0003	2-1-2020 18:50	Dagmar	2709407	service	Highlights medium	45
2020-0003	2-1-2020 18:50	Dagmar	2709407	service	Vibrance toner 60cc	1
2020-0004	3-1-2020 11:32	Dagmar	2682832	service	Heren knippen	1
2020-0005	3-1-2020 12:31	Dagmar	2911638	service	Woman | Cutting Delux	1
2020-0005	3-1-2020 12:31	Dagmar	2911638	service	Highlights medium	40
2020-0005	3-1-2020 12:31	Dagmar	2911638	service	Vibrance toner 60cc	1
2020-0005	3-1-2020 12:31	Dagmar	2911638	product	Grand Opening Shampoo	1
2020-0005	3-1-2020 12:31	Dagmar	2911638	product	Essential	1
2020-0006	3-1-2020 13:02	Dagmar	4071646	service	Heren knippen	1
2020-0007	3-1-2020 13:32	Dagmar	2676681	service	Heren knippen	1
2020-0008	3-1-2020 16:24	Dagmar	3023540	service	Woman | Cutting Delux	1
2020-0008	3-1-2020 16:24	Dagmar	3023540	service	Uitgroei verven	1
2020-0008	3-1-2020 16:24	Dagmar	3023540	service	Vibrance toner 120cc	1
2020-0009	3-1-2020 16:35	Dagmar	4243604	product	Picture Perfect	1
2020-0010	4-1-2020 11:33	Dagmar	2726032	service	Woman | Cutting Delux	1
2020-0010	4-1-2020 11:33	Dagmar	2726032	service	Uitgroei verven	1
2020-0010	4-1-2020 11:33	Dagmar	2726032	product	Rhassoul Oil	1

It would be great if someone could support me.

Regards, Fabian

[v-juanli-msft]

Hi @FH_1 

If Rocco_sprmnt21's answer help you slove problems, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know, we will work further to help you.

Best Regards

Maggie

[FH_1]

[v-juanli-msft]

Hi @FH_1 

Would you like to order the [quantity] per [type]?

If not, you could give expected result on your sample.

Best Regards

Maggie

[Anonymous]

add these lines to your code:

  top=5, 
  tab=Table.MaxN(Table.SelectRows(youTab, each [type]="service"),{"quantity"},top)
in
    tab

or, if you need a more complex filter condition, use some similar to this

Table.MaxN(Table.SelectRows(youTab, each [type]="service" and Text.Contains([desc],"Heren")),{"quantity"},top)

[FH_1]

Hi @Anonymous 

Tryed your soulition but the I got an error in my query. BTW this is my first time using a code in the table itself.

Can this be solved with a measure or a new colomun as well?

[Anonymous]

Ok. I will give another try. But you should clarify the relation between service and product.

If for the same order-id you have one service e multiple products, all is ok.

But in your table there are cases where for the same order-id you have many services and many products.

In this case what is the expected results?

[FH_1]

hi @Anonymous ,

In deed there can be several services and multiple products. What I need to show is the following:

Left tabel is my actual data from my database. What I want to express in my report is the right tabel. Is this possible?

If this is not possible it would ok to show the relation of the first service in an order to mutiple products.

Big thanks in advance.

[Anonymous]

try appending this in your advanced a editor just after the line where you loaded in your table (in my code called yourTab):

    #"Raggruppate righe" = Table.Group(yourTab, {"ID"}, {{"serv_and_prod", each  Table.Group(_,{"type","desc"},{"quantity", each _[quantity]{0}})}}),
    #"Tabella serv_and_prod espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "serv_and_prod", {"type", "desc", "quantity"}, {"type", "desc", "quantity"}),

    servizi = Table.SelectRows(#"Tabella serv_and_prod espansa", each ([type] = "service")),
    prodotti = Table.SelectRows(#"Tabella serv_and_prod espansa", each ([type] = "product")),


    mt = Table.NestedJoin(servizi,{"ID"},prodotti,{"ID"},"TypeProduct",JoinKind.LeftOuter),
    #"Tabella TypeProduct espansa" = Table.ExpandTableColumn(mt, "TypeProduct", {"type", "desc", "quantity"}, {"type.1", "desc.1", "quantity.1"})
in
    #"Tabella TypeProduct espansa"

[FH_1]

hi @v-juanli-msft ,

let me share an example, if you look to the table, you can see at order ID 2020 - 0010 that someone purchased 2 services and 1 product. I want to be able to show that if a customers bought service X, what are the products people bought as wel.

e.g.

hi @v-juanli-msft ,

let me share an example, if you look to the table, you can see at order ID 2020 - 0010 that someone purchased 2 services and 1 product. I want to be able to show that if a customers bought service X, what are the products people bought as wel.

e.g.

Service	Product
Woman Cutting	Product A 90 pcs
	Product B 75 pcs
	Product C 60 pcs
	Product D 30 pcs
	Product E 10 pcs

So I dont know if this is possible with having the data all in on table.

Regards, Fabian