Forum Discussion
How to separate items by rules?
hello everyone, i need a big help,
I have two tables:
a) table of products and region
b) name table
RULES:
- (PRINCIPAL) It is necessary to separate the total of products by region into equal amounts for the names in the Names table
- The product cannot repeat for one or more people.
- if the quantity of products per region is less than the quantity to be divided for each name, it must be completed with any other region
TABLE OF PRODUCTS:
TABLE OF NAMES:
davehus you had helped me in a very nice solution in other topic, can you please check the attached file?
Updated attached included.
Thanks to all for your help
Hi , Spotto
Based on your description, I have a general understanding of your needs. For your needs, you need to assign people, but one thing I haven't found a way to achieve is that after the assignment is completed, you also have to judge the [Region] of the person and then give priority to the product you own. For this point, I can only do it after the assignment is completed, and randomly assign it to the corresponding person according to the remaining products.
If this helps you, you can refer to it.Here are the steps you can refer to :
(1)We can add three blank query in Power BI Query Editor:"test_names":
let Source = NAMES, Custom1 = Table.Group(Source,"Region",{"test",(x)=>Table.AddIndexColumn(x, "Index", 1, 1, Int64.Type) }), #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"Name", "Index"}, {"Name", "Index"}), #"Added Custom" = Table.AddColumn(#"Expanded test", "Custom", (x)=> Table.SelectRows(PRODUCTS,(y)=> y[Region]=x[Region] and List.Contains({(x[Index]-1)* Number.RoundUp(Table.RowCount(PRODUCTS) / Table.RowCount(NAMES))+1..x[Index]* Number.RoundUp(Table.RowCount(PRODUCTS) / Table.RowCount(NAMES))},y[Index]) ) ), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.RoundUp(Table.RowCount(PRODUCTS) / Table.RowCount(NAMES)) - Table.RowCount([Custom]) ) in #"Added Custom1""Relay":
let Source = List.Combine(List.Transform(test_names[Custom],(x)=>x[count])), Custom1 = Table.SelectRows(PRODUCTS,(x)=> not List.Contains(Source,x[count]) ), #"Removed Columns" = Table.RemoveColumns(Custom1,{"Index", "count"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type) in #"Added Index""End result":
let Source = test_names, #"Removed Columns" = Table.RemoveColumns(Source,{"Custom", "Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Custom"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Custom] <> 0)), test = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type), Custom1 = Table.AddColumn(test,"end" , (x)=>{ if List.Sum(Table.SelectRows(test,(y)=> y[Index]< x[Index] )[Custom]) = null then 1 else List.Sum(Table.SelectRows(test,(y)=> y[Index]< x[Index] )[Custom])+1 ..List.Sum(Table.SelectRows(test,(y)=> y[Index]<= x[Index] )[Custom]) } ), #"Removed Columns1" = Table.RemoveColumns(Custom1,{"Custom", "Index"}), #"Expanded end" = Table.ExpandListColumn(#"Removed Columns1", "end"), #"Merged Queries" = Table.NestedJoin(#"Expanded end", {"end"}, Relay, {"Index"}, "Relay", JoinKind.LeftOuter), #"Expanded Relay" = Table.ExpandTableColumn(#"Merged Queries", "Relay", {"Product"}, {"Product"}), #"Removed Columns2" = Table.RemoveColumns(#"Expanded Relay",{"end"}), Custom2 = Table.ExpandTableColumn(Table.SelectColumns(test_names,{"Region","Name","Custom"}), "Custom", {"Product"}, {"Product"}) & #"Removed Columns2" in Custom2(2)The last result is as follows:
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
8 Replies
Hi,
Please show the exact result that you are expecting.
the result is what I expect:
I have 11 products that divided by 3 names = 4 products per name (this is in the ready attached file)
what I'm not able to do is separate the products by name and by region, and the name Marcelo can only receive products from the MS region up to a total of 4 products and if you don't have the total to receive products by region as in Marta's example she will receive the quantity available for her region (AB) and complete with products from another region.
Hi,
I tried but have not been able to understand your question. Sorry but someone else will surely be able to help you.
Hi , Spotto
For your needs, I think it can be implemented in Power Query Editor.
Here are the steps you can refer to :
(1)This is my test data :
For this data , every people have 13/3 --> 5 products.
(2)We can create two blank query in Power Query Editor: NAMES2 and PRODUCTS2
We can put this in "Advanced Editor":
PRODUCTS2:
let Source = PRODUCTS, Custom1 = Table.Group(Source,"Region", {"test",(x)=>Table.AddIndexColumn(x, "Index", 1, 1, Int64.Type) }), #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"Product", "Index"}, {"Product", "Index"}) in #"Expanded test"NAMES2:
let Source = NAMES, #"Removed Columns" = Table.RemoveColumns(Source,{"id"}), Custom1 = Table.Group(#"Removed Columns","Region",{"test",(x)=>Table.AddIndexColumn(x, "Index", 1, 1, Int64.Type) }), #"Expanded test" = Table.ExpandTableColumn(Custom1, "test", {"Name", "Index"}, {"Name", "Index"}), #"Added Custom" = Table.AddColumn(#"Expanded test", "Custom", (x)=> Table.SelectRows(PRODUCTS2,(y)=> y[Region]=x[Region] and List.Contains({(x[Index]-1)* Number.RoundUp(Table.RowCount(PRODUCTS) / Table.RowCount(NAMES))+1..x[Index]* Number.RoundUp(Table.RowCount(PRODUCTS) / Table.RowCount(NAMES))},y[Index]) ) ), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Product"}, {"Product"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Product", "Region", "Name"}) in #"Reordered Columns"(3)Then we can meet your need in 'NEEDS2' table , the result is as follows:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
thank you very much for your help, but I downloaded your file and tested it with other data and the result was the same.
The base NAME and PRODUCTS will change when updating the file and needed the result to be dynamic.I put below the file that I tested with another database.
Could you please take a look?
Hi , Spotto
I looked at your pbix file and I think there is nothing wrong with my statement.It is automatically generated according to logic.It may be that my understanding of your needs is biased.
In your "PRODUCTS" table , There are 20 rows. In youe "NAMES" table , there are 3 people.
So 20/3 --> 7 per people.
So every people can assign 7 products , but the people have different region. For the people name is "Roberta", his region is "MG", and in you "PRODUCTS" table which has only three MG region rows:
So i think the perople "Roberta" can assign this three products enven though the limit is 7 .
This is my computational logic, you can see if it doesn't meet your needs, and if so, you can correct my understanding of your needs.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
