cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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"

 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!
1 ACCEPTED SOLUTION
Super User

Yes, sure.

8 REPLIES 8
Super User

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]
)``````
Frequent Visitor

Hi @tamerj1 ,

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?

Super User

Yes, sure.

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

Frequent Visitor

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

Frequent Visitor

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.

Super User

hi @Nurzh

not sure if your case is complete. try like:

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

it worked like:

Frequent Visitor

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors