Forum Discussion
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.
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
Hi,
This calculated column formula works
Column = FIRSTNONBLANK(FILTER(VALUES(Buckets[BUCKET]),SEARCH(Buckets[BUCKET],Data[FULL_DATABASE_NAME],1,0)),1)
9 Replies
- HotChilli
Community Champion
Power Query. Split column by delimiter has ''from digit to non-digit" as an option.
That might do it
Sorry HotChilli , but I am using dummy data, in reallity I dont have any number on the middle.
- 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.
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 
smpa01Anonymous 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
- AlexisOlson
Super User
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], _) ) )
