25 February 2023

Yellow Excel or: How I Learned to stop worrying and Love PowerBI for Dynamics 365 BC with help from ChatGPT

This is a short note of how to add PowerBI reports to the Role Center page on Business Central

On most of the BC role centers you have this page part:


So lets get started; clicking "Get started" takes you to a short wizard that checks your PowerBI license and then presents you with the updated view:


Here you can select to see any reports already published to "Enable" them to show on your Role Center page


Maybe you want something else; lets do a small chart to see who has open approvals

Open PowerBI desktop and get data 


There are a number of API data sources for BC, but none that happen to work for this example


Time for a new API! Thankfully this is SUPER easy for simple pages.


Head to the Web Services page and use the "Create Data Set" button

Here are my choices to get a feed of approval entries 




Done - the new AP shows up in the list in BC and in PowerBI after a quick refresh



Precious data! 


Just to spice things up, here is an extra requirement 
  • The records to be approved are Purchase Invoices, Purchase Orders and Vendor
  • Vendors are not Documents in BC, so the Document Type column is blank for those approvals 
    • This will not do! Conditional column time : 
Its been way too long since I used PowerBI and I forgot how to do that kind of thing. Lets see if ChatGPT can help.

So the code for queries you can get to by clicking on "Transform Data" on the PowerBI desktop home ribbon. Then click this button:



Our beautiful code for the query, lets feed this to ChatGPT




And the reply 



Does it work? Not quite.  


But the beauty of ChatGPT is that it gave me a rough idea and this guided my next google searches on what to use instead of the made up "Text.IsEmpty" function.

Here is the final code with the extra column code that works.



Now we have the data - lets make a simple chart - make the chart the size of the whole page as it will scale in BC.


Looks like some people have got some approving to be getting on with!

Next Publish the PowerBI report to a suitable workspace


Select in BC - that's it done!