March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
Hi,
This calculated column formula works
Column = FIRSTNONBLANK(FILTER(VALUES(Buckets[BUCKET]),SEARCH(Buckets[BUCKET],Data[FULL_DATABASE_NAME],1,0)),1)
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], _)
)
)
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 |
Hi,
This calculated column formula works
Column = FIRSTNONBLANK(FILTER(VALUES(Buckets[BUCKET]),SEARCH(Buckets[BUCKET],Data[FULL_DATABASE_NAME],1,0)),1)
thanks a lot Ashish, your solution works!!
Many thanks to all of you 😗!!!
You are welcome.
@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
Sorry @HotChilli , but I am using dummy data, in reallity I dont have any number on the middle.
Power Query. Split column by delimiter has ''from digit to non-digit" as an option.
That might do it
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
119 | |
88 | |
73 | |
67 | |
49 |
User | Count |
---|---|
199 | |
140 | |
96 | |
77 | |
68 |