Many customers have shared how they would like to bring their features into Power BI reports but have not been able to - until now.
The recent release of the ArcGIS Connector for Power Automate, users can now reference hosted feature layers and convert them to CSV files that can be added to Power BI reports. Flows can be scheduled or manually triggered to create a CSV file to support your business intelligence needs in Power BI.
In this example, the flow is manually triggered and immediately creates a collection of variables to support the iteration through all the features in the feature layer.
Using the Initialize Variable action, the flow creates three variables supporting different aspects of the flow. The three variables are:
Next step in the flow is to get the data from the feature layer and iterate through all the records. To do this, use the Do until action to wrap the multiple records returned from the Get data from feature layer action.
The Get data from feature layer action needs a few user defined elements:
Now that the flow has data to work with, set the limit for the Do until. Click the Add dynamic content link in the Do Until action and select Dataset has additional data and set the conditional modifier to is equal to and define the modifier as no.
We are not done working in the Do until action - we need to set and append the variables defined at the beginning of the flow.
Set the Offset variable to the Current offset of the data
Setting the Header variable requires the flow to split the output from the data using the first row to get the column headers.
The value expression for setting the Header is:
split(outputs('Get_data_from_Feature_Layer')?['body'],decodeUriComponent('%0D%0A'))[0]
The Data variable is appended using an Append to string action replace function with the output from the dataset. This adds the geometry and attribute for each record which will be written to the resulting CSV file.
The value expression for setting the Data variable is:
replace(outputs('Get_data_from_Feature_Layer')?['body'],variables('Header'),'')
After the Do until action iterates through all the records, the flow will need to concatenate the header and data a write to to a file. To concatenate the Header and Data, the flow creates a new Result variable using the Initialize variable action and sets the value using a concatenation expression:
concat(variables('Header'),variables('Data'))
The final step is to write the Result to a CSV file using the Create file action.
The Create file action defines:
In the end, the flow looks like:
...and the visualization in Power BI looks like:
Tips & Lessons Learned
Thanks in advance and please let me know how this works for you or if you run into any issues.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.