Fill with digits

[azaterol]

Hello everyone,
I have a table "Customers_and_Suppliers" that contains an ID and the corresponding Customers_and_Suppliers_NR. I want to add a  custom column in this table. If there is a NR that matches the Supplier ID(secondtable), it should be filled with a maximum of 10 digits. There should be a 2 in the front left and the rest with 0. If there is no match, the same should be filled with a 1 and the rest with 0. Here's an example:

Initial situation:

ID		Customers_and_Suppliers_NR
3087		1
946		30
2642		3
28888		10
2		20
44		4
983764		2
19		20
222		40
99999		5

Supplier:

ID

10

20

30

40

50

Result:

ID		Customers_and_Suppliers_NR		AdressID
3087		1		1000003087
946		30		2000000946
2642		3		1000002642
28888		10		2000028888
2		20		2000000002
44		4		1000000044
983764		2		1000983764
19		20		2000000019
222		40		2000000222
99999		5		1000099999

Do you have an idea how I can solve this?

[Hoangechip910]

Hi

Try the calculate column below

Adress ID = if( Customer_and_Suppliers_NR in {"10","20","30","40","50"},2000000000 + VALUE(ID),1000000000 + VALUE(ID))

or

Adress ID = if( Customer_and_Suppliers_NR in {"10","20","30","40","50"},
                         REPLACE ("2000000000",10- LEN(ID) + 1,LEN(ID),ID coummn),

                           REPLACE ("1000000000",10- LEN(ID) + 1,LEN(ID),ID coummn) )

[tamerj1 · Accepted solution]

Ho @azaterol 

you can try

IF ( Table1[Customers_and_Suppliers_NR] IN VALUES ( Supplier[ID], 20000000000, 10000000000  ) + VALUE ( Table1[ID] ), 

[azaterol]

@tamerj1  works fantastic! Can you explain how this work? 

[tamerj1]

@azaterol 

It is just exactly as you read it. If the NR is in the supplier ID table then 20000000000 otherwise 10000000000 then add this number to the numeric value id table1[ID]