Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 2 tables :
Tab1, FIeld 11, content :
123456
234567
2345
234
Tab2, Field21, content
123456
23456
234
I would like to add a column in PowerBi Power Query to do what lookupvalue do.
Similar to lookupvalue(field12,
tab2[field21], mid(Tab1[field11],1, 6) ,
tab2[field21], mid(Tab1[field11],1, 5),
tab2[field21], mid(Tab1[field11],1, 4),
tab2[field21], mid(Tab1[field11],1, 3),
tab2[field21], mid(Tab1[field11],1, 2),
tab2[field21], mid(Tab1[field11],1, 1),
"not found")
Field11 Field12
123456 123456 coming from tab2 because Field11 equal to Field21
234567 23456 coming from tab2 because Field11 doenot equal to Field21 but 5 first char yes
2345 234 coming from tab2 because Field11 doenot equal to Field21 but 3 first char yes
234 234 coming from tab2 because Field11 equal to Field21
How to limplement this in Power BI/PowerQuery by adding a column ?
Thanks for your help
Solved! Go to Solution.
Hi @DoutGaet
My version of PowerBi is Version is same as yours : 2.85.985.0 64-bit (septembre 2020)
We can see Fuzzy in Merge function in Power Query Editor.
We can't get the result directly by Fuzzy Merge, we need to use other functions in Power Query Editor.
Here I will show you detailed steps:
1. Fuzzy Merge Tab1 and Tab2.
Expanded Tab2 and Add a Conditional Column.
Right click Field 21 column and replace Error , replace error as no found.
Finally remove Tab2.Field 21 column and remove rows which is null in Field 21 column.
Result:
You can download the pbix file from this link: Add Column lookupvalue
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DoutGaet
You may try Fuzzy matching in Merge Function.
I build two tables like yours to have a test.(I add 143 in Tab1 to show "no found" situation )
Copy M Query in Advanced Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U4rViVYCs8zhTBgDTBuaGCvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Field 11" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Field 11", type text}}),
#"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Field 11"}, Tab2, {"Field 21"}, "Tab2", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
#"Expanded Tab2" = Table.ExpandTableColumn(#"Merged Queries", "Tab2", {"Field 21"}, {"Tab2.Field 21"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Tab2", "Field 21", each if Text.StartsWith([Field 11], [Tab2.Field 21]) then [Tab2.Field 21] else null),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column", {{"Field 21", "no found"}}),
#"Sorted Rows" = Table.Sort(#"Replaced Errors",{{"Field 21", Order.Ascending}}),
#"Removed Top Rows" = Table.Skip(#"Sorted Rows",4),
#"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Tab2.Field 21"})
in
#"Removed Columns"
Result:
You can download the pbix file from this link: Add Column lookupvalue
For more info to Fuzzy match: Fuzzy Merge
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi RicoZhou,
The fuzzy search seems aappropriate to my need.
I do exactly whet you propose, but the merging didn't get any matching data.
It seems that fuzzy is not so fuzzy than expected.
My version of PowerBi is Version : 2.85.985.0 64-bit (septembre 2020)
and I cannot see the Option 'fuzzy parameter on my powerBi tool'.
could you help me ?
DoutGaet
Hi @DoutGaet
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Hi,
Many thansk for your explanation, but the resullt is not good enough.
sample :
in my file I have a 401000000 record.
In my ref file, I have 401, 4011, 4012, 4019.
The solution must return 401.
unfortunatly it doesn't !!
Fuzzy link seems not related 401 & 4010000.
thanks for your help.
Hi @DoutGaet
My version of PowerBi is Version is same as yours : 2.85.985.0 64-bit (septembre 2020)
We can see Fuzzy in Merge function in Power Query Editor.
We can't get the result directly by Fuzzy Merge, we need to use other functions in Power Query Editor.
Here I will show you detailed steps:
1. Fuzzy Merge Tab1 and Tab2.
Expanded Tab2 and Add a Conditional Column.
Right click Field 21 column and replace Error , replace error as no found.
Finally remove Tab2.Field 21 column and remove rows which is null in Field 21 column.
Result:
You can download the pbix file from this link: Add Column lookupvalue
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Pragati11
many thanks for your answer.
Merge queries could work if the link is matching, but It doent match.
If I need to rerun it for partial part of field11, check my request with sample of lookupvalue.
Any complementar idea ?
Cheers,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |