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
Nurzh
Frequent Visitor

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

Hi all,

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

Table "Document"

DocNumberDocCategory
61-2300-B-PID-2350-01Master
61-2300-B-PID-2350-01-07812Copy
61-2300-B-PID-2350-01-07812-21Copy

 

Desired result will be: 

DocNumberDocCategoryNew Column
61-2300-B-PID-2350-01Master61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812Copy61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21Copy61-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!
1 ACCEPTED SOLUTION

8 REPLIES 8
tamerj1
Super User
Super User

Hi @Nurzh 
Please refer to attached sample file with the solution

1.png

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

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? 

 

@Nurzh 

Yes, sure.

1.png2.png3.png4.png5.png

FreemanZ
Super User
Super User

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

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

 

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.

 

DocumentNumberDocumentTitleDocumentTypeShortCodeDocumentTypeDocumentSourceCategoryRevisionCodeFinalStatusNEW COLUMN
61-2300-B-PID-2350-01L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramMaster DocumentZ08Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyV01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07917DL-2350 TENGIZ FLS PIG RCVR (DESTRUCT)PIDPiping and Instrumentation DiagramProject Working CopyZ01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07917L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyZ01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-14398L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyZ03Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-00209L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyV01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyV01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-05209DL-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyZ03Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21D1L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyZ01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-05908PIPING AND INSTRUMENTATION DIAGRAM  L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyV01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812DL-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyV01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-05209L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyZ03Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-03210DL-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyZ01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21PL-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyZ01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-07812-21DL-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyV01Z61-2300-B-PID-2350-01
61-2300-B-PID-2350-01-03210L-2350 TENGIZ FLOWLINES PIG RECEIVERPIDPiping and Instrumentation DiagramProject Working CopyZ01Z61-2300-B-PID-2350-01

hi @Nurzh 

not sure if your case is complete. try like:

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

it worked like:

FreemanZ_0-1685425956075.png

 

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

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