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: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

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
358 Views
0
1 REPLY 1
some_bih
Super User
Super User

‎05-30-2023 04:37 AM

Hi @Anonymous 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
356 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All