Substring and Approximative Match

[Kasiop]

Hi,

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

Currently I have 2 sources and no exact matching.

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

[AlexisOlson]

I'd probably do this in Power Query:

This is the code for the custom column:

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

[HotChilli]

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.

[Kasiop]

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

Thanks,

SOURCE 1

FULL_DATABASE_NAME	BUCKET /SOURCE	WHAT I WANT (and I don't have)
landingconsumabledbprdaeranpill	aeranpill	aera
landingconsumabledbprdaerap6rs	aerap6rs	aera
landingconsumabledbprdaerap5op	aerap5op	aera
landingconsumabledbprdaerap3tr	aerap3tr	aera
landingconsumabledbprdaerap1te	aerap1te	aera
landingconsumabledbprdaeraplbou	aeraplbou	aera
landingconsumabledbprdaerasxyrl	aerasxyrl	aera
landingconsumabledbprdtelonip1ee	telonip1ee	teloni
landingconsumabledbprdtelanosp3ok	telanosp3ok	telanos
landingconsumabledbprdtelanosp1tr	telanosp1tr	telanos
landingconsumabledbprdtelanossmdl	telanossmdl	telanos

SOURCE 2

BUCKET

aera

telanos

teloni

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

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

[Kasiop]

thanks a lot Ashish, your solution works!! 

Many thanks to all of you 😗!!!

[Ashish_Mathur]

You are welcome.

[smpa01]

@Kasiop  Measure

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

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

[Kasiop]

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

[HotChilli]

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

That might do it