Dynamics 365
Microsoft Dynamics 365 is a cloud-based solution that provides businesses with a plethora of functionalities. Power BI is another powerful tool that allows users to visualize data and gain insights. When these two tools are combined, businesses can have a powerful tool to gain insights and make informed decisions. In this article, we will discuss how to use Power Query M language code for connecting to Dynamics 365 data source from inside Power BI.
What is Power Query M language?
Power Query M language is a functional, case-sensitive language used to transform data in Power BI. It is used to query, extract, and transform data from multiple sources. You can use Power Query M language to reshape, transform, and clean data from various sources before loading it into Power BI.
Connecting to Dynamics 365 data source from inside Power BI
To connect to the Dynamics 365 data source from inside Power BI, you need to follow these steps:
1. Open Power BI and click on the “Get Data” button on the Home tab.
2. In the “Get Data” window, select the “Dynamics 365 (online)” option under the “Online Services” category.
3. In the “Dynamics 365 (online)” window, enter the URL of your Dynamics 365 instance and select the authentication method.
4. Click on the “Connect” button to connect to the Dynamics 365 data source.
Once you have connected to the Dynamics 365 data source, you can use Power Query M language to transform the data and load it into Power BI.
Using Power Query M language to transform data from Dynamics 365
To use Power Query M language to transform data from Dynamics 365, you need to follow these steps:
1. Open Power BI and connect to the Dynamics 365 data source.
2. Click on the “Edit” button to open the Power Query Editor.
3. In the Power Query Editor, you can use the “Transform Data” tab to transform the data from Dynamics 365.
4. Use the “Add Column” option to add new columns to your data.
5. Use the “Group By” option to group your data based on specific fields.
6. Use the “Filter Rows” option to filter your data based on specific criteria.
7. Use the “Merge Queries” option to merge data from different sources.
8. Use the “Pivot Columns” option to pivot your data based on specific fields.
Sample Power Query M language code for connecting to Dynamics 365
Here is a sample Power Query M language code that you can use to connect to Dynamics 365 data source:
let
Source = Dynamics365Online.Contents(“
#”Added Items” = Source{[Name=”Items”]}[Data],
#”Expanded Product” = Table.ExpandRecordColumn(#”Added Items”, “Product”, {“ProductNumber”, “Name”}, {“Product.ProductNumber”, “Product.Name”}),
#”Expanded Sales Order” = Table.ExpandRecordColumn(#”Expanded Product”, “SalesOrder”, {“SalesOrderId”, “TotalAmount”, “CustomerId”, “DateOrdered”}, {“SalesOrder.SalesOrderId”, “SalesOrder.TotalAmount”, “SalesOrder.CustomerId”, “SalesOrder.DateOrdered”}),
#”Expanded Customer” = Table.ExpandRecordColumn(#”Expanded Sales Order”, “Customer”, {“CustomerId”, “Name”}, {“Customer.CustomerId”, “Customer.Name”}),
#”Removed Other Columns” = Table.SelectColumns(#”Expanded Customer”,{“SalesOrder.SalesOrderId”, “Product.ProductNumber”, “Product.Name”, “SalesOrder.TotalAmount”, “Customer.Name”, “SalesOrder.DateOrdered”}),
#”Renamed Columns” = Table.RenameColumns(#”Removed Other Columns”,{{“SalesOrder.SalesOrderId”, “SalesOrder ID”}, {“Product.ProductNumber”, “Product ID”}, {“Product.Name”, “Product Name”}, {“SalesOrder.TotalAmount”, “Total Amount”}, {“Customer.Name”, “Customer Name”}, {“SalesOrder.DateOrdered”, “Order Date”}})
in
#”Renamed Columns”
This code connects to Dynamics 365 and extracts data related to sales orders, products, and customers. It then expands the records and selects specific columns from each table before renaming them.
Conclusion
In conclusion, Power Query M language is a powerful tool that can be used to transform data from Dynamics 365 before loading it into Power BI. By using the sample code and the various transformation options available in Power Query, you can easily extract the data you need and gain valuable insights to make informed decisions for your business.
