Alternative to Merge tables

[rdnguyen]

Hi all,

I have table 1 contains about 30K keys, and table 2 to be merged to table 1 to match its content to only these 30K keys.

I did a left join but the result is super slow in refreshing as I know part of its due to 1:n relation. However, straight loading 1M records is pretty fast, but not sure why left join returned low performance.

Could you please suggest an alternative way to filter out just 30K's content?

Thanks and best regards.

[Anonymous]

You can also use Table.Join instead of Table.NestedJoin. Just make sure that you remove the "NewColumnName" parameter, and change "Table.NestedJoin" to "Table.Join". You might also want to make the key column distinct using Remove Duplicates. Even if there are no duplicates, this tells M that there are no duplicates. Table.Join is faster in my experience than the NestedJoin (for sure when you consider the time taken to expand the tables), especially with inner joins, and if your key and the other table's columns are both sorted ascending, then you can add the JoinAlgorithm.SortMerge parameter to the Table.Join function, and you will see your query finish processing in seconds. The NestedJoin function doesn't allow that parameter. And as if all that weren't enough, if you use an inner join with Table.Join, your key and foreign columns can have the exact same name, and Power Query will leave you with just one resulting key column--no need to rename anything.

Go forth and merge some stuff!

--Nate

[AlexisOlson]

Does an inner join rather than a left outer join make sense for what you're trying to do?

Without examples of what your data tables look like and what your ultimate goal is it's pretty hard to recommend any alternatives.

[rdnguyen]

Let me try, I thought I read somewhere they said doing Inner join is slower. Any other method, for example adding new column Yes/No to check if a key existed in the other table's key?

[AlexisOlson]

If you add that new column upstream as part of the physical table, then filtering may be faster than an inner join. I think it would make things slower if you defined that new column as part of your query though.