Autodesk Construction Cloud
Autodesk Construction Cloud is a cloud-based construction management software that provides a unified approach to project documentation, collaboration, and site management. Power BI is a business analytics service by Microsoft that provides interactive visualizations and business intelligence capabilities.
In this article, we will explore how to use Power Query M Language code to connect to the Autodesk Construction Cloud data source from inside Power BI.
Understanding the Power Query M Language
Power Query M Language is a functional, case-sensitive language used to manage data operations in Power BI. It is used to transform, clean, and merge data from different sources before loading it into Power BI for analysis.
The language is designed to be easy to read and write, even for non-programmers. It uses a step-by-step approach to data transformation, which allows users to visualize every step of the process.
Connecting to Autodesk Construction Cloud Data Source
To begin, we need to establish a connection to the Autodesk Construction Cloud data source. This can be done using the Power Query M Language code in Power BI.
To connect to the data source, we first need to open Power BI and select the ‘Get Data’ option from the Home tab. Next, we need to select ‘Web’ as our data source and enter the URL for the Autodesk Construction Cloud API.
Once the API is entered, we can use the Power Query M Language code to establish the connection. The code for connecting to Autodesk Construction Cloud looks like this:
let
Source = Json.Document(Web.Contents(“https://api.autodesk.com/project/v1/projects”)),
#”Converted to Table” = Record.ToTable(Source),
#”Expanded Value” = Table.ExpandRecordColumn(#”Converted to Table”, “Value”, {“id”, “name”, “status”}, {“Value.id”, “Value.name”, “Value.status”})
in
#”Expanded Value”
This code retrieves a list of all projects from the Autodesk Construction Cloud API and returns them as a table in Power BI.
Working with the Autodesk Construction Cloud Data Source
Once we have established a connection to the Autodesk Construction Cloud data source, we can begin working with the data. This involves transforming, cleaning, and merging the data to prepare it for analysis.
Here are some common operations that can be performed on the data using the Power Query M Language code:
Filtering Data
Filtering data is the process of selecting a subset of data that meets specific criteria. This can be done using the ‘Filter Rows’ function in Power Query.
For example, to filter all projects that are in progress, we can use the following code:
let
Source = Json.Document(Web.Contents(“https://api.autodesk.com/project/v1/projects”)),
#”Converted to Table” = Record.ToTable(Source),
#”Expanded Value” = Table.ExpandRecordColumn(#”Converted to Table”, “Value”, {“id”, “name”, “status”}, {“Value.id”, “Value.name”, “Value.status”}),
#”Filtered Rows” = Table.SelectRows(#”Expanded Value”, each ([Value.status] = “in progress”))
in
#”Filtered Rows”
This code filters the data to only show projects that are in progress.
Transforming Data
Transforming data involves changing the structure or format of the data to make it easier to work with. This can be done using the ‘Transform Data’ function in Power Query.
For example, to convert the project status column to a binary column (i.e., 1 for in progress and 0 for completed), we can use the following code:
let
Source = Json.Document(Web.Contents(“https://api.autodesk.com/project/v1/projects”)),
#”Converted to Table” = Record.ToTable(Source),
#”Expanded Value” = Table.ExpandRecordColumn(#”Converted to Table”, “Value”, {“id”, “name”, “status”}, {“Value.id”, “Value.name”, “Value.status”}),
#”Added Conditional Column” = Table.AddColumn(#”Expanded Value”, “Status Binary”, each if [Value.status] = “in progress” then 1 else 0)
in
#”Added Conditional Column”
This code adds a new column to the data table that converts the project status column to a binary format.
Merging Data
Merging data involves combining data from multiple tables or data sources to create a new table. This can be done using the ‘Merge Queries’ function in Power Query.
For example, to merge the project data with the data from the project team API, we can use the following code:
let
Source = Json.Document(Web.Contents(“https://api.autodesk.com/project/v1/projects”)),
#”Converted to Table” = Record.ToTable(Source),
#”Expanded Value” = Table.ExpandRecordColumn(#”Converted to Table”, “Value”, {“id”, “name”, “status”}, {“Value.id”, “Value.name”, “Value.status”}),
#”Merged Queries” = Table.NestedJoin(#”Expanded Value”, {“Value.id”}, team_data, {“project_id”}, “Merged”, JoinKind.LeftOuter),
#”Expanded Merged” = Table.ExpandTableColumn(#”Merged Queries”, “Merged”, {“id”, “email”, “role”}, {“Merged.id”, “Merged.email”, “Merged.role”})
in
#”Expanded Merged”
This code merges the project data with the data from the project team API and returns a new table in Power BI.
Conclusion
In this article, we have explored how to use Power Query M Language code to connect to the Autodesk Construction Cloud data source from inside Power BI. We have also looked at how to transform, clean, and merge the data to prepare it for analysis.
Using Power Query M Language code is a powerful way to manage data operations in Power BI. It allows users to quickly and easily transform, clean, and merge data from different sources, making it easier to derive insights and make informed decisions.
