Microsoft Power Query Limitations

Power Query is easy to use and appears to be the direction Microsoft is moving towards in regards to their BI reporting.  Business Analysts will find Power Query to be quick to learn, and easy to use, but you will want to proceed with caution.  You will find that in many instances you are going to need a solution that allows you to limit the data in your reports through simple parameters, as well as a way to manage the queries you write at a global level.

Currently Power Query does not have a user interface or simple way to pass parameter values to your query when retrieving data.  The ability to pass parameters is a requirement when it comes to big data.  What this means is you will not be able to effectively use Power Query to do an analysis of your transactional data (ex. GL or Checks).  Power Query data connections are established at a file level, so your Power Query connection only resides in your workbook.  Now, if you are on Office 365 Microsoft offers a repository in the cloud for shared queries within your business account. But this is something your IT Team will need to setup and manage for you.

So, before you start building a report utilizing Power Query understand your data and ensure that you can manage filters utilizing Slicers and do not need to pass parameters to limit your results.