Common Data Service
If you have been working with Power BI for a while, you might be familiar with the Common Data Service (CDS) data source. This data source provides a unified way to access and manage data across various Microsoft business applications, such as Dynamics 365, Power Apps, and more. In this article, we will explore how to connect to the CDS data source using Power Query M Language code from inside Power BI.
What is Power Query M Language?
Power Query M Language is a functional programming language used by Power Query to extract and transform data from various sources. It is a case-sensitive language that uses built-in functions and operators to perform data manipulation tasks. Power Query M Language is based on the F# programming language and is designed to be easy to learn and use.
Connecting to the CDS Data Source
To connect to the CDS data source from inside Power BI, we need to use the Power Query Editor. The Power Query Editor is a graphical user interface that allows us to create and edit Power Query M Language code. Here’s how to connect to the CDS data source using the Power Query Editor:
1. Open Power BI Desktop and select Get Data from the Home tab.
2. In the Get Data window, search for Common Data Service and select it from the list of available data sources.
3. In the Common Data Service window, enter the URL of your CDS environment and click OK.
4. In the next window, select the entities that you want to bring into Power BI and click Load.
This will create a new query in the Power Query Editor that contains the selected entities from the CDS data source. We can now use Power Query M Language code to manipulate this data and create reports.
Using Power Query M Language Code to Connect to the CDS Data Source
To connect to the CDS data source using Power Query M Language code, we need to use the Web.Contents function. This function allows us to make HTTP requests to a web service, which in this case is the CDS data source. Here’s an example of how to use the Web.Contents function to connect to the CDS data source:
let
cdsUrl = “https://
entityName = “
path = “/” & entityName & “?$select=”,
cdsFeed = Web.Contents(cdsUrl, [
RelativePath = path,
Headers = [
#”OData-MaxVersion”=”4.0″,
#”OData-Version”=”4.0″,
#”Accept”=”application/json;odata.metadata=minimal”,
#”Authorization”=”Bearer ” & Token
]
]),
cdsRecords = Json.Document(cdsFeed)[value]
in
cdsRecords
In this example, we first define the URL of our CDS environment and the name of the entity that we want to retrieve data for. We then construct the HTTP request path using the entity name and the select clause to retrieve all columns. Next, we use the Web.Contents function to make the HTTP request to the CDS data source. We provide the necessary headers, including the authorization token, and the function returns a feed of data in JSON format. We then use the Json.Document function to convert the JSON data into a table that we can use in Power BI.
Conclusion
Connecting to the CDS data source from inside Power BI using Power Query M Language code can be a powerful way to retrieve and manipulate data. By using the Web.Contents function, we can make HTTP requests to the CDS data source and retrieve data in JSON format. We can then use the built-in functions and operators of Power Query M Language to transform this data and create reports. With this knowledge, you can take your Power BI skills to the next level and create even more powerful data visualizations.
