Forum Discussion
Return Mutilple result in one row
Dear all,
I've have a Data 1
Data 1
| Product | Supplier | Quantity |
| Computer | AA | 2 |
| Computer | BB | 5 |
| Phone | CC | 6 |
and i want to make a report by power query with Mutiple Supplier and the Most sell:
Report
| Product | Supplier | Supplier | The Most sell supplier |
| Computer | AA | BB | BB |
| Phone | CC | CC |
Can anyone help me with that.
Thanks you!
NewStep= Table.Combine(Table.Group(Data1,"Product",{"n",each let a=Table.Group(_,"Supplier",{"n",each List.Sum([Quantity])}) in Table.FromRows({{[Product]{0}}&a[Supplier]&{Text.Combine(Table.SelectRows(a,each [n]=List.Max(a[n]))[Supplier],",")}},{"Product"}&List.Transform(List.Positions(a[n]),each "Supplier "&Text.From(_+1))&{"Most Sell Suppliers"})})[n])
2 Replies
NewStep= Table.Combine(Table.Group(Data1,"Product",{"n",each let a=Table.Group(_,"Supplier",{"n",each List.Sum([Quantity])}) in Table.FromRows({{[Product]{0}}&a[Supplier]&{Text.Combine(Table.SelectRows(a,each [n]=List.Max(a[n]))[Supplier],",")}},{"Product"}&List.Transform(List.Positions(a[n]),each "Supplier "&Text.From(_+1))&{"Most Sell Suppliers"})})[n])
It works! Thank you very much 😀
