Dynamics 365 Business Central: Summing up BC and Excel in PowerBI

Dynamics 365 Business Central: Summing up BC and Excel in PowerBI

We’ve got some Power BI to do this time around, we are going to dive a little bit into Power BI desktop and again, we're in the cloud, I keep drawing the cloud here, everybody's probably getting tired of that but anyways. We're working with Power BI but we're in the desktop version of Power BI, so the data is coming from BC and Excel, so with have two data sources coming into Power BI and the problem is when you have two data sources coming into one, you combine those data sources so that you can identify the number. If we just do that like here, we have a table and we have the number, item number right here and we got the quantity from BC, and then we have quantity from a vendor right. The quantity, the number comes from BC and the quantity in BC comes from there, however, the quantity from the vendor comes from Excel so ,what the system does automatically or Power BI, the desktop engine and actually the web engine as well, it combines the two these two things into one table for you, you don't have to do anything, this is actually a database programming effort that happens here combining these things together, they are joint.

But you didn't have to do that, all you have to do is say, the number is the same so go ahead and figure it out, which is great. Now what we want to do is we want to add a total to this, which adds up, so if this is QB and this is QV and this is QB + QV. But, where the hard part is: that this quantity here is coming from Business Central, this quantity here is coming from a vendor inside an excel sheet, and now you want to add these two together. So, the system is going like, things are retrieved, how can we do that?

To you, it looks simple, you can see them in the table right there and just say go ahead and add them together but to the system, it's much harder, except if we use measures. I'm going to go in and actually take a look at the power query editor a little bit and then I'm going to add a measure to add this together to show you how we can actually achieve this let's take a look okay so let's take a look at Power BI for a second and just focus on that I have this table here which is the number from Business Central, my item number, I got the inventory in Business Central, I got the quantity from the vendor excel sheet. If I look here, for example, I have the ILE’s which are item ledger entries, the items, and the vendor quantity. Vender quantity is coming from an excel sheet.

If I take a look here at the data, I can see the data in the tables and this is all looking like ERP data here and then I'm going to get into vendor quantity, obviously, that's just coming from an excel sheet. One thing about this data if I go back here, is the inventory here has decimals, however, the quantity here doesn't and that's slightly annoying, so I want to clean that up a little bit. I would like to have it just all be whole numbers because we don't really trade in fractional, another thing that's an issue is the name of the columns, would be nice to change those, so rename them. Finally, we have we should total it up because I want to know total what I have here, what's my total availability of each item, so how do we do that?

Total availability of each item
So, if I go here into home, we have something called edit queries, if I click on that, I get into a power query editor which is really nice, this the place where we bring in the data sources and we can manipulate the data source. It goes through these steps for each one of the data sources when it's bringing in the tables. My issue was with the inventory coming from BC, what I wanted to do was actually make it a whole number, I can go in here, just right click and say change type and I want to change it into a whole number and see it actually applies that on this data source, I can go into ILE’s and I could do the same for the inventory there, for each one of the ILE’s if I wanted to. If I click on the vendor quantity, I can see that that is already a whole number so that's good, now if I wanted to add a column here, to include the total of quantity and the inventory, that's not that easy. Here, if I if I go into add column, put a custom column, I could do that but the problem is that I cannot work with anything out of a different table here, I can only work with this table and I wanted to put a sum of this table and the other table.

I could come in here and say merge queries and merge the two queries of the items and the vendor quantity but that gets messy, it's not really the way to go. In order to get the sum of these two columns, I have to do that as a measure inside Power BI in the dashboard or actually in the visualization, I'm going to do that in a second; but there was one other thing that I wanted to do, was change the name of this, here we could just go ahead and rename this column and I'm going to call it vendor quantity and over here, I'll call this I will rename that as BC quantity; so you can see here we have renamed columns so what the system is doing when it's getting the data, it picks the source navigates to this table in the source, changes the type of quantity to whole numbers and renames the inventory to BC quantities. This is all it does when you're refreshing the data, so we just apply and close, now it's applying the changes. As you can see it goes through the loading of the data and creating everything. If I go back here I can see we have BC quantity inventor quantity, so this is starting to make more sense and it's cleaned up the decimals, now we're just missing to add these together.

In order to do that, we want to add a measure, if I just go in here and say new measure, we get a new measure in down in here, so it's measure equal,I'm going to call it total quantity and here I can sum up the items BC quantity and say that is plus the sum of vendor quantity, I closed that out and now it creates this measure. This measure is now affecting this table right here, so I should have it here, and it was called total quantity right here, now we can see that we have 8 of these 789 at vendor and total is 797, it's adding everything up. This here is the total quantity at vendor and BC so, we are now summing it up and the data makes sense, it looks a little bit better.