Active Directory
Power BI is a powerful business intelligence tool that allows you to connect to various data sources and create compelling visualizations and reports. One of the most common data sources that companies use is the Active Directory. In this article, we will explore how to connect to the Active Directory data source from inside Power BI using Power Query M language code.
What is Power Query M Language Code?
Power Query M language code is a programming language used by Power BI to connect to various data sources, transform and clean the data, and load it into the data model. It is a functional language that is easy to learn and use, even for non-programmers.
Why Connect to the Active Directory Data Source?
Active Directory is a directory service developed by Microsoft that stores information about objects on a network, such as users, computers, and groups. It is used by many organizations to manage their network resources, such as access to files, printers, and applications.
Connecting to the Active Directory data source from inside Power BI allows you to retrieve valuable information about your users, computers, and groups, and use it to create meaningful reports and visualizations.
How to Connect to the Active Directory Data Source
To connect to the Active Directory data source from inside Power BI, you will need to use the Power Query M language code. Here are the steps to do so:
1. Open Power BI and click on the “Get Data” button on the Home tab.
2. In the “Get Data” window, select “Blank Query” from the “Other” category and click “Connect.”
3. In the “Query Editor” window, click on the “View” tab and select “Advanced Editor.”
4. In the “Advanced Editor” window, paste the following Power Query M language code:
let
Source = ActiveDirectory.Domains(“yourdomain.com”),
users = Source{[Domain=”yourdomain.com”,Type=”users”]}[ObjectClass=”user”],
#”Expanded userPrincipalName” = Table.ExpandRecordColumn(users, “userPrincipalName”, {“userPrincipalName.1”}, {“userPrincipalName.1”}),
#”Removed Other Columns” = Table.SelectColumns(#”Expanded userPrincipalName”,{“distinguishedName”, “cn”, “sAMAccountName”, “userPrincipalName.1”}),
#”Renamed Columns” = Table.RenameColumns(#”Removed Other Columns”,{{“userPrincipalName.1”, “userPrincipalName”}})
in
#”Renamed Columns”
5. Replace “yourdomain.com” with your actual domain name in the “Source” line of the code.
6. Click “Done” and “Close & Apply” to load the data into Power BI.
Understanding the Power Query M Language Code
The Power Query M language code used to connect to the Active Directory data source is a simple code that retrieves user information from the domain and transforms it into a table. Here is a breakdown of what each line of the code does:
1. `let` – This line starts the Power Query M language code and defines a variable called “Source” that connects to the Active Directory domain.
2. `users = Source{[Domain=”yourdomain.com”,Type=”users”]}[ObjectClass=”user”]` – This line defines a new variable called “users” that retrieves only the user objects from the Active Directory domain.
3. `#”Expanded userPrincipalName” = Table.ExpandRecordColumn(users, “userPrincipalName”, {“userPrincipalName.1”}, {“userPrincipalName.1”})` – This line expands the “userPrincipalName” column of the “users” table into a new table with a single column called “userPrincipalName.1.”
4. `#”Removed Other Columns” = Table.SelectColumns(#”Expanded userPrincipalName”,{“distinguishedName”, “cn”, “sAMAccountName”, “userPrincipalName.1”})` – This line removes all columns except “distinguishedName,” “cn,” “sAMAccountName,” and “userPrincipalName.1” from the expanded table.
5. `#”Renamed Columns” = Table.RenameColumns(#”Removed Other Columns”,{{“userPrincipalName.1”, “userPrincipalName”}})` – This line renames the “userPrincipalName.1” column to “userPrincipalName.”
6. `in #”Renamed Columns”` – This line ends the Power Query M language code and returns the final table.
Conclusion
Connecting to the Active Directory data source from inside Power BI using Power Query M language code is a straightforward process that can yield valuable insights into your organization’s network resources. With a little bit of knowledge of the Power Query M language code, you can easily retrieve user information and use it to create meaningful reports and visualizations.
