Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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?
Solved! Go to Solution.
Ho @azaterol
you can try
IF ( Table1[Customers_and_Suppliers_NR] IN VALUES ( Supplier[ID], 20000000000, 10000000000 ) + VALUE ( Table1[ID] ),
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) )
Ho @azaterol
you can try
IF ( Table1[Customers_and_Suppliers_NR] IN VALUES ( Supplier[ID], 20000000000, 10000000000 ) + VALUE ( Table1[ID] ),
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]