Develop Power Query outside Power BI or Excel

[Lajouac]

Dear all,

I came across this Reddit post, and I'm interested in hearing what the Power Query community actually thinks about it.

The answers to the post basically claim that Power Query isn't built for complex transformations and suggest we should move to Python or SQL environments instead.

Like the post's author, I work primarily with multiple Excel files stored on SharePoint, which I transform using PQ to create solid star schemas for my reports. But the more I use it, the slower and more frustrating it becomes, whether in Excel, Power BI Desktop, or Power BI Service for Dataflows.

What are your thoughts on this?

Thanks a lot!

[cengizhanarslan · Accepted solution]

Power Query can struggle is when it’s used for heavy data engineering workloads, for example:

• Very large datasets (hundreds of millions of rows)

• Complex iterative logic or procedural transformations

• Transformations that don’t fold back to the source

• Large multi-stage pipelines where each step materializes intermediate data

In those situations, tools like SQL, Spark, or Python are often better because they are built for set-based processing at scale.

[Lashmi_25]

hi @Lajouac 

Power Query works very well for most Excel/SharePoint based transformations, especially for cleaning data, combining files, and preparing tables for reporting. Where performance usually becomes difficult is when the logic grows heavier for example multiple merges, large files, or many custom transformation steps.

A practical way to manage this is:

• keep early steps simple by removing unused columns and filtering rows first
• Reduce repeated merges where possible
• Use SQL or Python only when the transformation becomes too large or complex for Power Query to handle efficiently .
• For many reporting scenarios, Power Query is still a good fit, but using the right tool for heavier processing can make the overall model easier to maintain.

[cengizhanarslan · Accepted solution]

Power Query can struggle is when it’s used for heavy data engineering workloads, for example:

• Very large datasets (hundreds of millions of rows)

• Complex iterative logic or procedural transformations

• Transformations that don’t fold back to the source

• Large multi-stage pipelines where each step materializes intermediate data

In those situations, tools like SQL, Spark, or Python are often better because they are built for set-based processing at scale.

[Lajouac]

Thanks!

I am far below a million rows. If I have 100k, that would be the maximum I think.

Regarding your other points, I am not sure if I get them all, but I may sometimes have several consecutive queries, like 10 different queries to give me one final table. But I don't think this is too complex.

[v-aatheeque · Accepted solution]

Hi @Lajouac 

Currently, Power Query development is primarily designed to work within Power BI Desktop, Excel, or Power Query Online (Dataflows).

While it is technically possible to use tools like the Power Query SDK with VS Code to write M code, the setup can be complex especially when dealing with authentication and data source connections.

In practice, most users still rely on Power BI Desktop for development and debugging, and then reuse the logic in Dataflows or Fabric for production scenarios.

At this time, there isn’t a fully supported standalone IDE experience for Power Query outside Microsoft tools.

[v-aatheeque]

Hi @Lajouac 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[Lajouac]

Yes, Thanks a lot!

[m_dekorte]

Power Query deserves a little more appreciation, I think.
Rule of thumb for ETL: as far upstream as possible, and as far downstream as necessary.

Especially with SharePoint Online, performance often gets much better when using SharePoint.Contents or the Web.Contents connector instead of SharePoint.Files

[Lajouac]

Thanks for the SharePoint.Contents tip, I'll check if this makes things faster!