Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
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
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)
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?
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?
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.
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"
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.