Forum Discussion
Impactful IndividualPowerQuery: 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?
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"
5 Replies
- MarcelBeug
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"- ovetteabejuela
Thanks Marcel, at least I have 2 choices now.
