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
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
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:
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