Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
THENNA_41
Post Partisan
Post Partisan

column and another column last letter comparison

I have two column In the table first column name ID  and next column name pd-id .  I am trying to compare First column  value and Next column last num  if match  both value match its return  True  and both value not match its  return False

Example :

 

ID         Pd-id 

1          ID01-PS-001

 

 1         ID01-PS-001

 

 2         ID01-PS-001

 

 1          ID01-PS-002

 

 3         ID01-PS-003

  

  2         ID01-PS-002

 

  3          ID01-PS-002

 

  4          ID01-PS-004

 

Expected output:

 

  

ID         Pd-id                        Result 

1          ID01-PS-001             True 

 

 1         ID01-PS-001             True

 

 2         ID01-PS-001             False

 

 1          ID01-PS-002             False

 

 3         ID01-PS-003              True

  

  2         ID01-PS-002             True

 

  3          ID01-PS-002            False

 

  4          ID01-PS-004            True

 

 

I am looking for support . thanks in advance.

1 ACCEPTED SOLUTION
v-yetao1-msft
Community Support
Community Support

Hi @THENNA_41 

First , you need to extract the last num in your Pd-id column . Create a calculated column :

Column = RIGHT('Table'[Pd-id],1)

Change data type of the column to whole number .

Ailsamsft_0-1668996702603.png

Then create a calculated column to compare the First column  value and Next column last num .

Result = IF('Table'[Column]='Table'[ID],TRUE,false)

You will get a result like this :

Ailsamsft_1-1668996899092.png

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yetao1-msft
Community Support
Community Support

Hi @THENNA_41 

First , you need to extract the last num in your Pd-id column . Create a calculated column :

Column = RIGHT('Table'[Pd-id],1)

Change data type of the column to whole number .

Ailsamsft_0-1668996702603.png

Then create a calculated column to compare the First column  value and Next column last num .

Result = IF('Table'[Column]='Table'[ID],TRUE,false)

You will get a result like this :

Ailsamsft_1-1668996899092.png

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yetao1-msft  Thank you so much . 

FreemanZ
Super User
Super User

Try to add a new column with the code below:

Result=

IF(CONTAINSSTRING([id], [pd-id]), TRUE, FALSE)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors