Dynamics 365 Business Central: Microsoft Stack Explained, Excel & Power BI

Dynamics 365 Business Central: Microsoft Stack Explained, Excel & Power BI

We have the cloud and we got Business Central and Power BI connecting together and so I have the inventory data coming from Business Central into Power BI, we a list here with items and we saw quantities etc. and item category so, one of the things that we saw in the item list here, is that some of these had negative quantities and that's not good right, then we have some of them that actually had no item category so, it was filled up for some and then empty and then filled up for some. We want to fix this, I want to fix the quantities that are negative, and I want to fix the items that have no item category, so I have a complete set. Then I want to refresh the data from Business Central into Power BI to see my changes,  I'm going to go up to Business Central, fix the problem, refresh it into Power BI and see the updated changes, let's see how that works and one more thing, we're going to use another app to fix the data called Excel.

Alright so let's take a look at where we were, we did have a few items that were negative inventory and we had a few items that didn't have item category code and we want to fix that. I actually fixed most of them and I have one left here, the guest section is at negative 5 as you can see, so I want to go ahead and fix the guest section and I can go into Business Central just take a look at the list view so it's easier to find, and the year I have its order of quantity on hand, I see on negative 5, then I want to fix that to be zero. Let's just say this was a mistake, I go here into adjust inventory, put this at zero, hit OK and now it's zero. I want to see this in Power BI, just go back here and hit refresh; it's already connected, so all I have to do is hit refresh and it refreshes my report, now it's gone to zero. I have no negative inventory, now what about the item category codes? we want to fix that, I want to use Excel to fix that which makes it really cool.

Edit Item cards in Excel
I'll go here into edit in Excel and I get the item card connected to Excel, notice that I did add the item category code here just for viewing; you can do that using personalization; but here, if I click Excel, I get the item card across, just hit enable editing, it will login to my account so, Excel is now connected to the cloud and to BC the data source. There's no configuration that had to be done, all I did was edit in Excel and I get a list of my items. Now, this list here contains not the same columns as the list that I was looking at, the item list, it actually has separate set of columns and in this list unfortunately I don't have the item category, so how do I fix that? because I really want to fix the item category.

I can go in here into design and take a look at this table it's connected and just hit edit and it shows me all the fields that are available right here and then all the fields are visible up here, if I go into available fields I can find my item category, I just double click on that and that pops up here, basically it's right here and I'm going to push it as far up as I can, let's get it into visibility, put it right next to the description. If I hit update, item category code shows up now there's nothing going to be in here and that's of course not true because I know I have item category codes in the system, but I have to click here on done and refresh the data and then it brings the data in again.

Now it is here, so for the magic moment I'm going to put the medium drip coffee into cups for now and coffee beans that's just the cups category, the bundle is going to go into miscellaneous and the guests section is a chair, sectional chair. Conference bubble is miscellaneous, conference package miscellaneous, okay, so I now have item category codes for everything and I want to publish this, so it gets published and its successful. I go into Business Central and refresh Business Central just by hitting f5 or up here, we should get the latest data and look at that, all my item categories are fixed, now, if I go to the Power BI I can just hit refresh and we get everything in here perfectly. So now we have fixed the data so that all the item categories are filled out. If we do any reporting by item category, then you know we're not going to have any empty values which is exactly what we want.