How to separate items by rules?

[Spotto]

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.

RowAllocation (2).pbix 

Thanks to all for your help

[v-yueyunzh-msft]

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

[Spotto]

@v-yueyunzh-msft  

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?

1011group.pbix 

[v-yueyunzh-msft]

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

[Spotto]

@v-yueyunzh-msft 

Your solution was very cool, but there are some points that I would like your help:
->  We have 20 lines for 3 people so each person must receive 7 (round up).
-> If any person does not have the amount of 7 (20/3) to receive, they can receive from another REGION. Example of Roberta who has only 3 product for MG and should receive 7 products in total.

I thought of this logical sequence to solve and I would like your opinion if you could do it:

1) - Enter an index by name, like this:

2) Fill the REGION (MS) to MARCELO until the total = 7, when you finish filling (MS = 7) or not, go to the next step;

3) In the case of ROBERTA that has two REGIONS (I added this rule because it can happen in some cases), fill in the MG region until = 7 and if you don't have enough region fill in MS until ROBERTA receives 7 regions (MG or MS). If you succeed or not, go to the next step.    4) Execute the same rule 3) for the other names (id)
5) At the end of all ids, check after distributing the names(id) by REGION if the total by name/id=7 and if the grand total = 20. Return again to id =1 and check id by id if it is equal to 7, in the case of id not being = 7 fill in with any other region that has not been distributed yet

I believe that with these 5 rules above we will be able to solve all problems and that I can sleep peacefully 😆

Thanks again for your help

[v-yueyunzh-msft · Accepted solution]

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

[Ashish_Mathur]

Hi,

Please show the exact result that you are expecting.

[Spotto]

@Ashish_Mathur  

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.

[Ashish_Mathur]

Hi,

I tried but have not been able to understand your question.  Sorry but someone else will surely be able to help you.