Intune Data Warehouse
Microsoft Intune is a cloud-based service that enables organizations to manage their mobile devices and PCs. The Intune Data Warehouse is a feature of the service that provides access to a SQL Server database that contains a wealth of information about the devices and users in your organization. Power BI is a business analytics service that provides interactive visualizations and business intelligence capabilities. In this article, we will explore how to use Power Query M language to connect to the Intune Data Warehouse data source from inside Power BI.
Prerequisites
Before we begin, make sure you have the following:
– A Power BI account
– Access to the Intune Data Warehouse
– Knowledge of Power Query M language
Connecting to the Intune Data Warehouse Data Source
To connect to the Intune Data Warehouse data source from inside Power BI, follow these steps:
1. Open Power BI Desktop and click on the “Get Data” button on the Home tab of the ribbon.
2. In the “Get Data” dialog box, select “SQL Server” and click “Connect”.
3. In the “SQL Server database” dialog box, enter the server name and database name for the Intune Data Warehouse. You can find this information in the Intune portal under “Reports > Data Warehouse”.
4. Select “SQL Server” as the authentication method and enter your credentials.
5. Click “Connect” to establish a connection to the Intune Data Warehouse data source.
Using Power Query M Language Code
Once you have established a connection to the Intune Data Warehouse data source, you can use Power Query M language code to transform and shape the data.
Querying Data from the Intune Data Warehouse Data Source
To query data from the Intune Data Warehouse data source, follow these steps:
1. In the Power BI Desktop, click on the “Transform data” button on the Home tab of the ribbon.
2. In the Power Query Editor, click on the “New Source” button in the “Home” tab of the ribbon.
3. In the “New Source” dialog box, select “SQL Server” and click “Connect”.
4. In the “SQL Server database” dialog box, enter the server name and database name for the Intune Data Warehouse. You can find this information in the Intune portal under “Reports > Data Warehouse”.
5. Select “SQL Server” as the authentication method and enter your credentials.
6. Click “Connect” to establish a connection to the Intune Data Warehouse data source.
7. In the Power Query Editor, you can now use the “Data” tab of the ribbon to query the data from the Intune Data Warehouse data source.
Transforming Data with Power Query M Language Code
Power Query M language code provides a powerful way to transform and shape the data from the Intune Data Warehouse data source. Here are a few examples of how to use Power Query M language code to transform data:
– To remove columns from a table, use the “Table.RemoveColumns” function.
– To filter rows in a table, use the “Table.SelectRows” function.
– To rename columns in a table, use the “Table.RenameColumns” function.
– To combine multiple tables into a single table, use the “Table.Combine” function.
Summary
In this article, we explored how to use Power Query M language to connect to the Intune Data Warehouse data source from inside Power BI. We also looked at how to use Power Query M language code to transform and shape the data from the Intune Data Warehouse data source. By using these techniques, you can create powerful visualizations and gain valuable insights into the devices and users in your organization.