Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

Reply
Nurzh_17
Frequent Visitor

Remove certain part of string based on value from other column

‎05-29-2023 05:06 AM

Hello all, 

I have issue with removing certain part of string value in my column. 

Example: 

Table "Document"

DocumentNumberDocumentSourceCategory
61-2300-B-PID-2350-01Master Document
61-2300-B-PID-2350-01-07812Project Working Copy
61-2300-B-PID-2350-01-07917DProject Working Copy
61-2300-B-PID-2350-01-07917Project Working Copy
61-2300-B-PID-2350-01-14398Project Working Copy
61-2300-B-PID-2350-01-00209Project Working Copy
61-2300-B-PID-2350-01-07812-21Project Working Copy
61-2300-B-PID-2350-01-05209DProject Working Copy
61-2300-B-PID-2350-01-07812-21D1Project Working Copy
61-2300-B-PID-2350-01-05908Project Working Copy
61-2300-B-PID-2350-01-07812DProject Working Copy
61-2300-B-PID-2350-01-05209Project Working Copy
61-2300-B-PID-2350-01-03210DProject Working Copy
61-2300-B-PID-2350-01-07812-21PProject Working Copy
61-2300-B-PID-2350-01-07812-21DProject Working Copy
61-2300-B-PID-2350-01-03210Project Working Copy

 

Desired New Column that will identify us the Master Document Number of this copies: 

 

DocumentNumberDocumentSourceCategoryDocument cut
61-2300-B-PID-2350-01Master Document61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812Project Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07917DProject Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07917Project Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-14398Project Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-00209Project Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21Project Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-05209DProject Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21D1Project Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-05908Project Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812DProject Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-05209Project Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-03210DProject Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21PProject Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21DProject Working Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-03210Project Working Copy61-2300-B-PID-2350-01

 

Thank you!

 

Message 1 of 2
190 Views
0
1 REPLY 1
some_bih
Super User
Super User

‎05-30-2023 04:37 AM

Hi @Nurzh_17 one possible solution could be to create new column in Power query using M function Text.Start

(I recognize pattern from your picture: first 21 character should retrive):

New column Document cut code is

= Table.AddColumn(#"Promoted Headers", "Document cut", each Text.Start([DocumentNumber],21))

I hope this help

some_bih_0-1685446646790.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Message 2 of 2
188 Views
0

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

View All