cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Kasiop
Helper II
Helper II

Substring and Approximative Match

Hi,

 

I have an issue and I am really stuck here, let me explain it:

Currently I have 2 sources and no exact matching.

Kasiop_2-1637939016051.png

 

What do I want to achieve?

I want to match SOURCE1.bucket/source with SOURCE2.bucket because at the end I want to have all the information separatly.

database > source> table> bucket

 

let me know if I can just "split" this column SOURCE1.bucket/source but I there is no logic...

 

Many thanks,

Naïma

1 ACCEPTED SOLUTION

Hi,

This calculated column formula works

Column = FIRSTNONBLANK(FILTER(VALUES(Buckets[BUCKET]),SEARCH(Buckets[BUCKET],Data[FULL_DATABASE_NAME],1,0)),1)

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
AlexisOlson
Super User
Super User

I'd probably do this in Power Query:

AlexisOlson_0-1637967344510.png

 

This is the code for the custom column:

(row) =>
    List.Max(
        List.Select(
            SOURCE2[BUCKET],
            each Text.Contains(row[FULL_DATABASE_NAME], _)
        )
    )
HotChilli
Super User
Super User

What about using Text.Range with Text.Length from the Source column to get the bucket from the last column?

Post some more realistic data (not a picture) if you want more help, please.

Below what I could give as data, I hope it will be useful.

 

Thanks,

 

SOURCE 1

FULL_DATABASE_NAMEBUCKET /SOURCEWHAT I WANT (and I don't have)
landingconsumabledbprdaeranpillaeranpillaera
landingconsumabledbprdaerap6rsaerap6rsaera
landingconsumabledbprdaerap5opaerap5opaera
landingconsumabledbprdaerap3traerap3traera
landingconsumabledbprdaerap1teaerap1teaera
landingconsumabledbprdaeraplbouaeraplbouaera
landingconsumabledbprdaerasxyrlaerasxyrlaera
landingconsumabledbprdtelonip1eetelonip1eeteloni
landingconsumabledbprdtelanosp3oktelanosp3oktelanos
landingconsumabledbprdtelanosp1trtelanosp1trtelanos
landingconsumabledbprdtelanossmdltelanossmdltelanos

 

SOURCE 2

BUCKET
aera
telanos
teloni

 

Hi,

This calculated column formula works

Column = FIRSTNONBLANK(FILTER(VALUES(Buckets[BUCKET]),SEARCH(Buckets[BUCKET],Data[FULL_DATABASE_NAME],1,0)),1)

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

thanks a lot Ashish, your solution works!! 

 

Many thanks to all of you 😗!!!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Kasiop  Measure

Measure =
MAXX (
    FILTER (
        CROSSJOIN ( tbl1, tbl2 ),
        CONTAINSSTRING ( tbl1[BUCKET /SOURCE], tbl2[BUCKET] ) = TRUE ()
    ),
    [BUCKET]
)

smpa01_0-1637957694407.png

 

Calculated Column

Column = 
VAR _0 =
    FILTER (
        CROSSJOIN ( tbl1, tbl2 ),
        CONTAINSSTRING ( tbl1[BUCKET /SOURCE], tbl2[BUCKET] ) = TRUE ()
    )
VAR _1 =
    MAXX (
        FILTER ( _0, [FULL_DATABASE_NAME] = EARLIER ( tbl1[FULL_DATABASE_NAME] ) ),
        [BUCKET]
    )
RETURN
    _1

 

smpa01_1-1637957742759.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Kasiop
Helper II
Helper II

Sorry @HotChilli , but I am using dummy data, in reallity I dont have any number on the middle.

HotChilli
Super User
Super User

Power Query. Split column by delimiter has ''from digit to non-digit" as an option.

That might do it

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors