Assign a value to each row in a table from list

[albatross19]

Currently I have a table with customers and their orders to which I need to assign a fulfillment center (list). I need to assign a fulfillment center based on row position. I have a total of 3 fulfillment centers.

For example, customer 1 or row 1 gets assign fullfillment center 1. Row 2 gets assign fulfillment center 2, then row 3 gets assign fulfillment center 3. Then I need to restart the loop by assigning row 4 to fulfillment center 1.

See example below:

Fulfillment Center

Chicago

Seattle

Boston

Index	Customer	Orders	Fulfillment Center
1	Mike	1	Chicago
2	Alicia	3	Seattle
3	Steven	1	Boston
4	Maria	4	Chicago
5	Louie	2	Seattle

I am open to solve this problem either by using PowerQuery or DAX (calculated column)

[albatross19]

Thank you!

[MahyarTF]

Hi,

If we consider the first Table as a Sheet11 and next one as a Sheet9 :

- Create an Index column for each table :

- In the Sheet9 (Customer) table, Create a Custom Column as a below statement :

= Table.AddColumn(#"Added Custom1", "link Col", each if Table.RowCount(Sheet11) >= [Index] then [Index] else if Number.Mod([Index], Table.RowCount(Sheet11) ) = 0 then 3 else Number.Mod([Index], Table.RowCount(Sheet11) ))

Now if create a relationship between two tables based on the "Index" column and "Link Col" :

You could have the below visual :

Hop it will be useful

[albatross19]

Thank you!

[amitchandak · Accepted solution]

@albatross19 , Add an index column in tables 1  start with 0  - power query

then create a new column in table 2  - power query

new index = Number.Mod([Index], 3)

Then merge the tables using index and new index in power query

Merge Tables (Power Query) : https://www.youtube.com/watch?v=zNrmbagO0Oo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=16

merge: https://radacad.com/append-vs-merge-in-power-bi-and-power-query