The Microsoft Power Query custom function is a valuable tool to carry in your toolbox when working with Microsoft Power BI. Creating a custom function can provide you with a way to deal with difficult data situations. A custom function is written in the Power Query M formula language which allows you transform data coming from many sources, including data from Microsoft Dynamics 365 Business Central.
You use Microsoft Power BI with Business Central. But, every time you try to get the salesOrderLines table using the Standard APIs v2.0 you get an bad request error message because Business Central is expecting you to provide the id or document id. How do you get around this issue?
There are a number of options to get around the issue. But, the best way within Power BI is to create a Power Query custom function. Let's go ahead and go through the process to create a custom function to get the sales order lines into Power BI.
1. Let's first get the sales order table in Power BI. In Power BI click on Home in the upper menu and then click on Get data in the lower menu. Enter business central and click on the top selection and then click on Connect. Drill down until you find the company you want and then drill down in the Standard VPIs v2.0. Click on the salesOrders table. Click the Load button. You now have salesOrders in Power BI as shown in the Data pane.
2. Let's edit salesOrders and get some connection information that we will need later on in our process. Under the Data pane right mouse on salesOrders and select Edit query. Click on Home in the upper menu and then click on Advanced editor in the lower menu.
3. We need to copy four lines of M code from the salesOrders. Copy the highlighted code shown in blue from the Advanced editor into your Notepad. Cancel out of the Advanced editor in Power BI.
4. Add the code shown in red before and after the code you already have in your Notepad. This is the Power Query M code that will take a documentid as input and return all salesOrderLines from Business Central that match.
= (documentId as text) =>
let
salesOrderLines_table = v2.0{[Name="salesOrderLines",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(salesOrderLines_table, each ([documentId] = documentId))
in
#"Filtered Rows"5. We will now create a new custom function and paste in the Power Query M code from the Notepad. Click Home on the upper menu and then under New Source click Blank Query. Copy and paste all the code in your Notepad into the Power Query Editor and press enter. If done correctly you will see the prompt to enter the documentID as shown below.
6. Change the query name under the Query Settings Properties to getSalesOrderLines. Then in the upper menu click on File and then Apply.
7. Next we are going to put into play the custom function we just created. Click on Home in the upper menu and then New Source. Do the same as you did in step 1 above. Once again we want to select the salesOrders table (Yes, the salesOrders table).
8. We need to rename the id column in the salesOrders table. Right mouse on the id column name and rename the column to idOld.
9. Click on Home in the upper menu and then select Choose Columns in the lower menu. Remove all check-marks except for the idOld column. Click on the OK button.
10. Click on Add Column in the upper menu and click on Invoke Custom Function in the lower menu. Select getSalesOrderLines in the Function query box and then select idOld in the documentId box. Click on the OK button.
11. Click on the Expand button next to the getSalesOrderLines column header. The button has arrows going left and right.
12. After clicking the Expand box you will see a list of fields. You need to un-check the use original column as prefix box at the bottom. Then click the OK button.
13. We now need to remove the idOld column as it is redundent information with the documentId column from the sales order lines. Right mouse on the idOld column and click Remove.
14. Click on Home in the upper menu and then click on Close and Apply to save the query.
15. Right mouse on salesOrders (2) table in the Data pane on the right side. Rename the table to salesOrderLines. You now see in the Data pane both the salesOrders and salesOrderLines tables. We are done.
