Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

PowerQuery: Merge Queries

It appears that Merge Queries  are case-sensitive, what are you recommendations for composite keys that are of text type?

 

build another column that transforms the column to lower-case/uppers for both tables? or is there a much more elegant way of doing this?

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

It would be prefered to have numeric keys.

If you need to stick to text, then I think your proposal is the best suggestion.

 

Table.NestedJoin has an additional parameter "keyEqualityComparers" that would suggest the possibility to use case-insensitive comparison, but this doesn't seem to work with local evaluation (maybe it works with a database source and query folding).

This parameter was discussed here.

 

The following code performs a case sensitive left outerjoin from Table1 to Table2, however "performs"  is not very applicable as this is a real performance killer and I wouldn't recommend it for any tables with more than 100 records.

 

let
    Source1 = Table.Buffer(Table1),
    Source2 = Table.Buffer(Table2),
    #"Added Custom" = Table.AddColumn(Source1, "Table2", (Record1) => Table.SelectRows(Source2, (Record2) => Comparer.Equals(Comparer.OrdinalIgnoreCase, Record1[Key1], Record2[Key2]))),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Added Custom", "Table2", {"Key2", "Value2"}, {"Key2", "Value2"})
in
    #"Expanded Table2"

 

Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
MarcelBeug
Community Champion
Community Champion

It would be prefered to have numeric keys.

If you need to stick to text, then I think your proposal is the best suggestion.

 

Table.NestedJoin has an additional parameter "keyEqualityComparers" that would suggest the possibility to use case-insensitive comparison, but this doesn't seem to work with local evaluation (maybe it works with a database source and query folding).

This parameter was discussed here.

 

The following code performs a case sensitive left outerjoin from Table1 to Table2, however "performs"  is not very applicable as this is a real performance killer and I wouldn't recommend it for any tables with more than 100 records.

 

let
    Source1 = Table.Buffer(Table1),
    Source2 = Table.Buffer(Table2),
    #"Added Custom" = Table.AddColumn(Source1, "Table2", (Record1) => Table.SelectRows(Source2, (Record2) => Comparer.Equals(Comparer.OrdinalIgnoreCase, Record1[Key1], Record2[Key2]))),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Added Custom", "Table2", {"Key2", "Value2"}, {"Key2", "Value2"})
in
    #"Expanded Table2"

 

Specializing in Power Query Formula Language (M)

Thanks Marcel, at least I have 2 choices now.

Its an old post.

We do have a fuzzy merge option now.

Use [IgnoreCase=true, Threshold=1.0] should do a case agonostic merge.

 

Anonymous
Not applicable

Thanks @modi123p .

 

It resolved my needs and those who are looking for the solution,

 

KarthikKV_0-1626241163469.png

You can also refer to the below article,

 

https://www.ehansalytics.com/blog/2020/4/27/case-insensitive-merges-in-power-query

 

Thanks,

Karthik

Anonymous
Not applicable

But as Mat mention in artcilce

 
Unfortunately this seems to ignore + - signs.

I have three items:

19719201
19719201+
19719201-

With this fuzzy matching it's returning all three for item 19719201

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.