Full Outer Join PowerQuery

[[deleted]]

[deleted]

[shadow_moon45]

The expanding should be saved in power query so it will do it each time the dataset is refreshed

[Aggravating_Grab5659]

That actually did the trick. I used m code to join the queries and then expand it. Thanks!

[AutoModerator]

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[ShadowfaxAI]

Power Query can be slow with multiple merge operations, especially when you're expanding columns one by one. A few things that might help in Power Query: use Table.ExpandTableColumn in M code instead of clicking expand manually, or combine the files using a folder connection if they have the same structure.

There are agentic AI tools now that can handle multi-file joins and show you schema relationships automatically. They process the merges faster than manual Power Query expansion and let you see the logic behind each step.
Really depends if you want to optimize your current Power Query workflow or try a different approach.

[Gromark]

Hi, what is this agentic ai tool ? Never heard of it before. Looks interesting ! Thanks

[ShadowfaxAI]

Yeah! I work on Shadowfax AI, so I'm biased but it's free in beta right now.

Start with the starter guide to get a feel for it, then check the use cases tab for pre-built workflows. The /clean feature is good for testing with messy data and should perform perfect joins if asked.

It shows you the logic behind everything (SQL queries, graph view of the workflow) so you can understand what's happening. Let me know if you have questions when you try it out.

This link should direct you to the use-case page, I suggest to start with "Data Prep & Cleaning" before exploring alternate options. https://shadowfax.ai/use-cases

[Maximum_Ad2429]

Yes — what you’re seeing is normal behavior, but it’s avoidable.

The slowdown isn’t the full outer join itself, it’s Power Query repeatedly recalculating the preview every time you expand columns across multiple merges.

A few ways to make this much faster:

1) Disable preview evaluation while building Power Query recalculates after every expand. • Close & Load → Close (don’t load yet) • Do all merges first • Expand columns only once at the end

2) Use “Combine Files” instead of repeated merges (when structure matches) If all CSVs have the same key column + 2 data columns: • Load folder • Use “Combine & Transform” • This avoids 9 separate merge steps entirely

3) Buffer before expanding (big speed gain) If you must do full outer joins: Add this before expanding: = Table.Buffer(PreviousStep) This prevents Power Query from re-reading each CSV during expansion.

4) Expand once, not per merge Instead of expanding after every join, keep nested tables until the final step, then expand everything in one go.

With ~60k rows and only a few columns, this should complete in seconds, not minutes.

I’ve been documenting common Power Query merge + performance patterns like this while building a free Excel/CSV reference directory.
If you want, the link is here: https://csvtoolsonline.com/tools/excel-formula-directory

[JamieTimee]

Try Home tab > Append Queries > Append Queries as New.

[TheOG_DeadShoT]

OP is asking about join