Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hii All
I have a probelm. I have to tables with two columns.
Table 1
Personname: Costumername 1 ( Smith)
Costumername 2 ( Green)
Costumername 3 ( Summer)
Table 2
PERNAME: Costumername 1 ( Jerry Paul Clarkson)
Costumername 2 (Jenny Summer Smith)
Costumername 3 (Mr Clark Paul Jackson)
What i need is if there is a contain make = 1 and if not then 0.
I have tried a lot of functions but nothing helped. In other words i need a contains function that are checking the table 1 colum with the table 2 column.
Thanks and best wishes
Donb87
Solved! Go to Solution.
@DONB87
Please do Trim and Clean to Table1 column then use the following M code and refer to attached sample file for more details.
= Table.AddColumn(#"Cleaned Text","Custom", each if Text.Contains(Text.Upper(Text.Combine(#"Table2"[PERNAME],",")),Text.Upper([Personname])) then 1 else 0)
@DONB87
Please do Trim and Clean to Table1 column then use the following M code and refer to attached sample file for more details.
= Table.AddColumn(#"Cleaned Text","Custom", each if Text.Contains(Text.Upper(Text.Combine(#"Table2"[PERNAME],",")),Text.Upper([Personname])) then 1 else 0)
Hii @tamerj1
Unfortunatelly im getting only 0, because it compares the entire cell and not contains 😞 Maybe other ideas, that can help my to resolve my probelm.
Best wishes
Donb87
Actually no. Text.Contains compares contains not whole text.
Again, power query is case sensitive.
Hii @tamerj1
What i need is this:
I have tried your code but im getting only 0.
Thanks a lot for your help!
BEst
Donb87
@DONB87
Power Query is case sensetive. First capitalize both columns then add the custom column.
Hi @DONB87
Please refr to attached sample file with the solution.
Check =
INT (
NOT ISEMPTY (
FILTER (
Table2,
CONTAINSSTRING ( Table2[PERNAME], Table1[Personname] )
)
)
)
= Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains(Text.Combine(#"Table2"[PERNAME],","),[Personname]) then 1 else 0)
User | Count |
---|---|
52 | |
33 | |
19 | |
16 | |
15 |
User | Count |
---|---|
94 | |
84 | |
37 | |
22 | |
20 |