So if you’re like me, you are starting to use Microsoft’s Power BI even more. It’s a great tool, and really helps with Data Visualizations especially if you have the right data model to report from. The difference between having the right data model or not are simply mind boggling! Make sure you have the correct, flattened, related data model in place first, instead of becoming frustrated with Power BI tool or poor results. Over the next few months, I will continue to blog some more about how to create a data model that will make you more successful when using Power BI for your Dynamics 365 data.

In today’s world, it really pays to know SQL. A majority of the modeling type work is done thru SQL query statements. With the ability to create virtual SQL Servers or within Azure, there’s no excuse for not being able to get an environment to learn in. So did you know you could write the SQL query statements in SQL Management Studio and then re-use the same exact query in Power BI as a direct query? This is especially useful when you have already setup the Dynamics 365 Data Export service to Azure SQL for your Dynamics 365 reporting needs.

So what’s the magic button? Well, I was writing SQL query statements to flatten out a Dynamics 365 model for use with Power BI, and like every good client, they always wanted to add more, and more fields to the data set and thinking of new things.  When I originally built the data model, I inserted my original SQL query statement into Power BI report, it was automatically converted to the syntax  for Power BI: a M query. This meant I could not easily move or alter my SQL query statements into Power Bi after the report was created or had to learn more M Query. Or so I thought…

So here’s what you see when you look at the Power BI Data Query. Select Edit Queries

 

 

 

 

 

Next Select Advanced Editor

 

 

 

Within the advanced editor you will see the revised M query built from your SQL query. Not very user friendly editor IMO . I would prefer to see my SQL query statements. I was cutting and pasting these into notepad so I could add to the #if statements for additional fields, often ending with an error when pasted back in! I wished there was a way I could just paste in my newly updated SQL query statement…. There is 🙂

 

 

Notice the nice little gear on the right column next to source?? Give it a Click…

 

 

Boom! Now you can see your original SQL Query and edit it directly! This will save a ton of time as I can now just update my SQL query from my SQL Management Studio via a simple cut and paste! Something really simple but hidden on the data interface! I hope this tip helps save you some time in the future.. Enjoy!