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.