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

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

Reply
Nurzh
Frequent Visitor

Find the text from string and remove remain part

Hi all, 

I would like to create a new column in a table that will show me number of Master Document for Project Copies. 

Example: 

Table "Document"

Document NumberDocument Category
61-2300-P-5090Master Document 
61-2300-P-5090-07812Project Working Copy 
61-2300-P-5090-05908PProject Working Copy 
61-2300-P-5090-05908T1Project Working Copy 
61-2300-P-5090-05908TProject Working Copy 
61-2300-P-5090-07812-21Project Working Copy 

 

Desired New Column that will identify us the Number of Master Document of this copies: 

Document NumberDocument CategoryNew column (Master Documents)
61-2300-P-5090Master Document 61-2300-P-5090
61-2300-P-5090-07812Project Working Copy 61-2300-P-5090
61-2300-P-5090-05908PProject Working Copy 61-2300-P-5090
61-2300-P-5090-05908T1Project Working Copy 61-2300-P-5090
61-2300-P-5090-05908TProject Working Copy 61-2300-P-5090
61-2300-P-5090-07812-21Project Working Copy 61-2300-P-5090

 

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

 

I tried this code, but it does not work correctly: 

 

document master =
VAR CurrentCategory = Document[DocumentSourceCategory]
VAR CurrentNumber = Document[DocumentNumber]
VAR IsMasterDocument = CurrentCategory = "Master Document"
VAR MasterNumber =
    IF(
        IsMasterDocument,
        CurrentNumber,
        LEFT(CurrentNumber, IFERROR(FIND("-", CurrentNumber) - 1, LEN(CurrentNumber))  
        )
    )
RETURN
    MasterNumber
 
 
Thank you for any help! 
1 ACCEPTED SOLUTION

@Nurzh,

I am not sure it will work with your big dataset, but you can try the following in Power Query:

1) Sort your table by "Document Number".

2) Add a conditional column.

barritown_0-1685528008380.png

3) Apply "Fill Down" form the newly created column.

 

barritown_1-1685528100441.png

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

View solution in original post

5 REPLIES 5
Nurzh
Frequent Visitor

Hi @barritown , 

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? 

 

Hi @Nurzh,

I knew my solution is greedy in terms of resources, but 6-7 hours - wow.

I will think how to opimize my [DAX] solution - not sure that I will be able to turn hours into minutes, but I'll try. Before that, could you please clarify how many "master" entries you have among those 2.8 million rows?

As for the Power Query solution, maybe it is worth duplicating this post in the "Power Query" section of this forum - I guess Power Query gurus live there. 🙂

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Hi @barritown , 

There is approximately 2.7 million  of them "Master Documents". 

Sure I will try to duplicate post in Power Query section, thank you, hope we can find the best solution 🙂

@Nurzh,

I am not sure it will work with your big dataset, but you can try the following in Power Query:

1) Sort your table by "Document Number".

2) Add a conditional column.

barritown_0-1685528008380.png

3) Apply "Fill Down" form the newly created column.

 

barritown_1-1685528100441.png

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

barritown
Super User
Super User

Hi @Nurzh,

Please try this calculated column versus your data:

barritown_0-1685454607830.png

And in plain text for convenience:

 

Document Master = 
VAR CurrentNumber = [Document Number]
RETURN MINX (
        FILTER (
            ADDCOLUMNS (    
                FILTER ( ALL ( Document ), Document[Document Category] = "Master Document" ),
                "Match",
                VAR CurrentMaster = [Document Number]
                RETURN CONTAINSSTRING ( CurrentNumber, CurrentMaster ) ),
            [Match] = TRUE () ), 
        [Document Number] )

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.