Add Column lookupvalue

[DoutGaet]

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   

[v-rzhou-msft]

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. 

[DoutGaet]

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

[v-rzhou-msft]

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

[DoutGaet]

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.

[v-rzhou-msft · Accepted 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. 

[Pragati11]

HI @DoutGaet ,

Try using merge queries in Query Editor. See article below:

https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616...

Thanks,

Pragati

[DoutGaet]

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,