Dynamics 365 Online
Microsoft Dynamics 365 is a cloud-based business solution that provides organizations with the tools they need to manage customer relationships, financials, operations, and human resources. Power BI, on the other hand, is a business analytics service that enables users to visualize and analyze data from a wide range of sources. By integrating Power BI with Dynamics 365, users can gain powerful insights into their business operations and make informed decisions.
One of the ways to connect Power BI to Dynamics 365 is by using the Power Query Editor, which is a data transformation and cleansing tool built into Power BI. In this article, we’ll take a look at how to use the Power Query M Language code to connect to the Dynamics 365 Online data source from inside Power BI.
Setting Up the Connection
Before we can connect Power BI to Dynamics 365, we need to set up the connection. To do this, we’ll need to obtain the Dynamics 365 URL and the authentication credentials.
Obtaining the Dynamics 365 URL
To obtain the Dynamics 365 URL, follow these steps:
1. Log in to your Dynamics 365 account.
2. Click on the gear icon in the upper-right corner and select “Advanced settings”.
3. In the left-hand navigation menu, click on “Settings” and then “Customizations”.
4. Click on “Developer Resources”.
5. Copy the URL from the “Instance Web API” field.
Obtaining the Authentication Credentials
To obtain the authentication credentials, follow these steps:
1. Log in to your Dynamics 365 account.
2. Click on the gear icon in the upper-right corner and select “Options”.
3. In the left-hand navigation menu, click on “Security”.
4. Click on “Application Passwords” and then “New”.
5. Enter a name for the password and click “Save”.
6. Copy the password and keep it in a safe place.
Creating the Connection in Power Query Editor
Now that we have the Dynamics 365 URL and authentication credentials, we can create the connection in Power Query Editor. To do this, follow these steps:
1. Open Power BI Desktop and click on “Get Data”.
2. Select “Dynamics 365 (online)” from the list of available data sources and click “Connect”.
3. In the “From OData Feed” dialog box, enter the Dynamics 365 URL that you obtained earlier and click “OK”.
4. In the “Access an OData feed” dialog box, select “Organizational account” as the authentication method and enter your Dynamics 365 username and the application password that you obtained earlier. Click “Connect”.
5. Power Query Editor will load the data from the Dynamics 365 entity that you selected. You can use the editor to transform and cleanse the data as needed.
Using the Power Query M Language Code
If you want to automate the process of connecting to the Dynamics 365 Online data source from inside Power BI, you can use the Power Query M Language code. This code can be saved in a file with a .pq extension and then imported into Power BI. Here’s an example of the Power Query M Language code for connecting to the Dynamics 365 Online data source:
let
Source = OData.Feed(“https://
#”
in
#”
To use this code, replace `
Conclusion
By using the Power Query M Language code, users can easily connect Power BI to Dynamics 365 and automate the process of loading data from the online data source. The Power Query Editor provides a powerful tool for data transformation and cleansing, which can help users gain valuable insights into their business operations.
