Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors