Dataverse
Power BI is a powerful business intelligence tool that allows users to analyze data, create reports, and share insights with others. One of the key features that makes Power BI so versatile is its ability to connect to a wide range of data sources, including the Microsoft Dataverse.
The Dataverse is a cloud-based data storage and management platform that is part of the Microsoft Power Platform. It allows users to store and manage data using a range of customizable business apps, including Dynamics 365 and Power Apps.
To connect to the Dataverse data source from inside Power BI, you can use Power Query, a data transformation and cleansing tool that is built into Power BI. Power Query uses a formula language called “M” to perform data transformations and manipulations.
In this article, we will explore how to use Power Query’s M language to connect to the Dataverse data source from inside Power BI.
Step 1: Get the Dataverse URL
Before you can connect to the Dataverse data source, you will need to obtain the URL of your Dataverse environment. You can find this URL by navigating to the Power Platform admin center and selecting the relevant environment.
Step 2: Open Power BI and Navigate to the Query Editor
Once you have the Dataverse URL, open Power BI and navigate to the Query Editor. The Query Editor is where you can perform data transformations and manipulations using Power Query’s M language.
To open the Query Editor, select “Edit Queries” from the Home tab in the Power BI ribbon.
Step 3: Create a New Query
In the Query Editor, select “New Source” from the Home tab in the Power Query ribbon. This will open the “Get Data” dialog box.
In the “Get Data” dialog box, select “Web” as the data source type. This will allow you to connect to the Dataverse data source using a URL.
Step 4: Enter the Dataverse URL
In the “Web” dialog box, enter the Dataverse URL that you obtained in Step 1. Make sure to include the “https://” protocol at the beginning of the URL.
Step 5: Select the Relevant Dataverse Entity
Once you have entered the Dataverse URL, select the relevant entity that you want to connect to. This will allow you to retrieve data from that entity and use it in your Power BI reports.
Step 6: Transform and Cleanse the Data
Once you have connected to the Dataverse data source, you can use Power Query’s M language to perform data transformations and manipulations. This can include cleaning and filtering the data, merging data from multiple sources, and creating calculated columns.
To apply a transformation to the data, select the relevant column or table in the Query Editor and select the “Transform Data” option from the Home tab in the Power Query ribbon. This will open the “Formula Bar,” where you can enter Power Query’s M language formulas.
Step 7: Load the Data into Power BI
Once you have transformed and cleansed the data, you can load it into Power BI by selecting the “Close & Apply” option from the Home tab in the Power Query ribbon. This will close the Query Editor and load the data into Power BI’s data model.
Conclusion
Connecting to the Dataverse data source from inside Power BI is a powerful way to analyze and visualize your data. By using Power Query’s M language, you can perform data transformations and manipulations to transform the raw data into insights and actionable information. With these steps, you can easily connect to the Dataverse data source and start building powerful reports and dashboards in Power BI.
