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

[Nurzh]

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!

[tamerj1]

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

[Nurzh]

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? 

[tamerj1 · Accepted solution]

@Nurzh 

Yes, sure.

[FreemanZ]

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

[Nurzh]

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

[Nurzh]

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

[FreemanZ]

hi @Nurzh 

not sure if your case is complete. try like:

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

it worked like:

[Nurzh]

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