topic Re: Find string from another column and remove remain part of this string in DAX Commands and Tips

Find string from another column and remove remain part of this string

Nurzh — Tue, 30 May 2023 05:13:20 GMT

Hi all,

I would like to find the occurence of string in one column, and remove the remain part. Example:

Table "Document"

DocNumber	DocCategory
61-2300-B-PID-2350-01	Master
61-2300-B-PID-2350-01-07812	Copy
61-2300-B-PID-2350-01-07812-21	Copy

Desired result will be:

DocNumber	DocCategory	New Column
61-2300-B-PID-2350-01	Master	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812	Copy	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21	Copy	61-2300-B-PID-2350-01

I have tried this formula, but it does not work correctly.

document master = VAR CurrentCategory = Document[DocCategory] VAR CurrentNumber = Document[DocNumber] VAR IsMasterDocument = CurrentCategory = "Master" VAR MasterNumber = IF( IsMasterDocument, CurrentNumber, LEFT(CurrentNumber, IFERROR(FIND("-", CurrentNumber & "-", FIND("-", CurrentNumber) + 1) - 1,LEN(CurrentNumber))) ) RETURN MasterNumber

One more thing is the lenght of Master Documents can be different and we need to check it by Category.

Thank you for any help!

Re: Find string from another column and remove remain part of this string

FreemanZ — Tue, 30 May 2023 05:37:50 GMT

hi Nurzh

do you have other columns?

if that is all you have, you can just try:

column =
MAXX(
FILTER(data, data[DocCategory]="Master"),
data[DocNumber]
)

Re: Find string from another column and remove remain part of this string

Nurzh — Tue, 30 May 2023 05:46:00 GMT

Hi, FreemanZ

Yes I have, here is the full table with another example:

Desired new column I make it Bold.

The thing is that we need to identify Master Document value from Copy Document Numbers.

DocumentNumber	DocumentTitle	DocumentTypeShortCode	DocumentType	DocumentSourceCategory	RevisionCode	FinalStatus	NEW COLUMN
61-2300-B-PID-2350-01	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Master Document	Z08	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	V01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07917D	L-2350 TENGIZ FLS PIG RCVR (DESTRUCT)	PID	Piping and Instrumentation Diagram	Project Working Copy	Z01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07917	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	Z01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-14398	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	Z03	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-00209	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	V01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	V01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-05209D	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	Z03	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21D1	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	Z01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-05908	PIPING AND INSTRUMENTATION DIAGRAM L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	V01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812D	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	V01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-05209	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	Z03	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-03210D	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	Z01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21P	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	Z01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21D	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	V01	Z	61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-03210	L-2350 TENGIZ FLOWLINES PIG RECEIVER	PID	Piping and Instrumentation Diagram	Project Working Copy	Z01	Z	61-2300-B-PID-2350-01

Re: Find string from another column and remove remain part of this string

FreemanZ — Tue, 30 May 2023 05:53:03 GMT

hi Nurzh

not sure if your case is complete. try like:

Column = MAXX( FILTER( data, data[DocumentSourceCategory]="Master Document" ), data[DocumentNumber] )

it worked like:

Re: Find string from another column and remove remain part of this string

Nurzh — Tue, 30 May 2023 05:53:11 GMT

This is not suitable for my case, I need to check occurence of every string Master Document in rows where Category = Copy

Re: Find string from another column and remove remain part of this string

Nurzh — Tue, 30 May 2023 06:02:30 GMT

Tried this formula, it does not suitable for my case, as I have many various document copies. This takes only max value of all master documents.

The thing is we need to search for occurence of Document Number where Category = Master in Document Numbers where Category = Copy

Re: Find string from another column and remove remain part of this string

tamerj1 — Tue, 30 May 2023 08:19:34 GMT

Hi Nurzh
Please refer to attached sample file with the solution

Master Documents = MAXX ( FILTER ( 'Table', 'Table'[Document Category] = "Master Document" && CONTAINSSTRING ( EARLIER ( 'Table'[Document Number] ), 'Table'[Document Number] ) ), 'Table'[Document Number] )

Re: Find string from another column and remove remain part of this string

Nurzh — Wed, 31 May 2023 04:30:50 GMT

Hi tamerj1 ,
Thank you for your solution,

it really works, but I have 2.8 million rows in my table, and now almost 6-7 hours I am waiting for the result, it just stuck on "Working on it"...

Is there any other ways to realise it, maybe in Power query?

Re: Find string from another column and remove remain part of this string

tamerj1 — Wed, 31 May 2023 06:10:20 GMT

Nurzh

Yes, sure.