Cherwell

Power BI is a powerful data visualization tool that can help businesses make sense of their data and gain insights that can drive decision-making. One key feature of Power BI is its ability to connect to a wide range of data sources, including Cherwell, a popular IT service management platform. In this article, we will explore the Power Query M Language code for connecting to the Cherwell data source from inside Power BI.

What is Power Query M Language?

Power Query is a data transformation and data preparation engine built into Power BI. It allows users to connect to various data sources, transform and shape the data, and load it into the Power BI data model. Power Query M Language is the proprietary language that is used to create queries in Power Query. It is a functional, case-sensitive language that is used to define data transformations.

Why Use Power Query M Language for Connecting to Cherwell?

Connecting to Cherwell from Power BI using the Power Query M Language has several advantages. These include:

– Flexibility: With Power Query M Language, you can connect to multiple Cherwell data sources and combine data from multiple tables or views in a single query.

– Data Cleaning and Transformation: Power Query M Language provides a range of functions and transformations that can be used to clean and prepare the data for analysis. For example, you can remove duplicates, split columns, and change data types.

– Performance: Power Query M Language is optimized for performance, which means that queries can be executed quickly and efficiently, even with large data sets.

Connecting to Cherwell using Power Query M Language

To connect to Cherwell using Power Query M Language, you must first obtain the necessary connection details. These include the Cherwell server address, port number, username, and password. Once you have these details, you can follow these steps:

1. Open Power BI and click on “Get Data” on the Home tab.

2. In the “Get Data” window, select “Cherwell” from the list of available data sources.

3. In the “Cherwell” dialog box, enter the Cherwell server address, port number, username, and password.

4. Click “Connect” to establish a connection to the Cherwell data source.

5. In the “Navigator” window, select the tables or views that you want to use in your query.

6. Click “Edit” to open the Power Query Editor.

7. In the Power Query Editor, you can perform various data transformations using the Power Query M Language.

Power Query M Language Code for Connecting to Cherwell

Here is an example of the Power Query M Language code for connecting to Cherwell:


let
    Source = Cherwell.Contents("https://Cherwell.example.com", 80),
    CherwellTables = Source{[Name="Tables"]}[Data],
    #"Filtered Rows" = Table.SelectRows(CherwellTables, each ([Name] = "Incident")),
    IncidentTable = #"Filtered Rows"{0}[Data],
    #"Expanded Data" = Table.ExpandTableColumn(IncidentTable, "Data", {"CoreFields/ID", "CoreFields/OwnedByTeam/PublicID", "CoreFields/Status", "CoreFields/OwnedBy/FullName", "CoreFields/ShortDescription"}, {"ID", "Owned By Team ID", "Status", "Owner Name", "Short Description"})
in
    #"Expanded Data"

In this code, we first establish a connection to the Cherwell data source using the Cherwell.Contents function. We then select the “Tables” table and filter it to include only the “Incident” table. We then select the “Data” column from the incident table and expand it to include the relevant fields. Finally, we return the expanded data as the output of the query.

Conclusion

Power Query M Language provides a powerful and flexible way to connect to the Cherwell data source from inside Power BI. By using Power Query M Language, you can perform various data transformations and combine data from multiple tables or views. We hope that this article has provided you with a basic understanding of how to use Power Query M Language to connect to Cherwell and prepare data for analysis in Power BI.