cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Fill with digits

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?

1 ACCEPTED SOLUTION
Super User

you can try

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

4 REPLIES 4
Frequent Visitor

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) )

Super User

you can try

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

Helper V

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

Super User

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]

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors