Power Query merge creating multiple replicate columns - weird never seen this behavior before!

[009co]

I have used merge 100's of times in many situations .. left, inner, full joins, etc but have never seen this weird behavior before.

Data Table - location column has many values (fact table)

Location Table - location column has unique values (dimension table)

I am merging Location Table to Data Table using a left join on the location columns and then expanding only one Location Table column which looks something like:

= Table.ExpandTableColumn(#"Merged Queries", "Location Table", {"Address"}, {"Location Table.Address"})

But after loading the query, it is actually loading all of the Location Table columns which were not even checked in the exapand table step, and in fact creates duplicates and triplicates of some of the eg Location Table.Address.1, Location Table.Address.2, Location Table.Address.3, plus all of the other columns in the Location Table which were not even checked.

I did a test changing the "Default column name prefix (optional)" value from "Location Table" to "Test" and then I got one column "Test.Address" but all of the others too eg Location Table.Address.1, Location Table.Address.2, Location Table.Address.3, plus all of the other columns in the Location Table which were not even checked!

It is just the weirdest thing.  Has anybody else seen something similar? No idea what is happening and see no obvious troubleshooting clues.

[v-angzheng-msft · Accepted solution]

Hi, @009co 

Based on the information above, what seems to be happening here is that the merge operation is getting all the columns instead of the matching ones. It's not clear if it's because the special column you want to expand isn't checked but the entire table.
It would be easier to analyze what happened if the M query code was attached here.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Have you tried clearing the cache in the Query Options?

--Nate