| Product(s): | WaterSight |
| Version(s): | 10.00. |
| Area: | Documentation |
Overview
Power BI is an interactive data visualization software product developed by Microsoft, focusing primarily on business intelligence. It is part of the Microsoft Power Platform. The existing dashboards of WaterSight can be extended with custom Power BI dashboards to achieve utility-specific insights. More information about licencing and how to create and publish PowerBI reports can be found in this article: Creating and Publishing PowerBI reports.
The goal of this article is to detail with one example how GIS data that is available in WaterSight can be fetched and used for the purpose of building reports in PowerBI.
Pulling GIS data into PowerBI
- Load the Report thru Power BI Desktop
- Add a Custom Get function from WaterSight connector in to your Power BI report
- Select Home > Transform Data
- Select the Custom Get function in the left panel
- Fill out the parameters as follows:
- First field: api/v1/GeographicFeatures/DataFeaturesBulk?digitalTwinId=XXX&dataType=YYY
- Where: XXX = Digital Twin ID | YYY: Name of GIS layer in WaterSight (Pipes, Zones, etc)
- Region(optional): leave blank for projects done in Azure based in US-EST. For European projects put weu
- First field: api/v1/GeographicFeatures/DataFeaturesBulk?digitalTwinId=XXX&dataType=YYY
- Click Invoke
A new Query is loaded in the left panel (Invoked Function)
- Select the Invoked Function table
- Select Add Colunm > Index Colunm > From 1
- Select the Colunm “Value” and Click in the Separate button
and choose “Expand to New Rows”
- Select again the Colunm “Value” and Click in the Separate button
and keep the Colunms checked and keep the option to Use Original Colunm name as Prefix enabled.
- Click Ok
At this time you have a query organized into 3 columns: Attribute, Value and Index. The Index field helps you to understand from which shapefile element the information belongs to. The rows within the red rectangle in the picture above are attributes and values for the very first Element of the GIS layer.
From here the next steps will transpose the table in a way to get a final table with columns with values for each attribute.
- Select the Colunm “Value.Attribute”
- Select Transform > Group By
- Select Basic
- Select the Value.Attribute field
- Select “All rows” as Operation
- Click Ok
- Select Transform > Transpose
- Select Transform > Use First Row as Header
- For each relevant Colunm, right click in the Colunm header and select Add as New Query
It will create a new Query in the left panel.
- Select it and left click in the Cell named “Table”.
It expands the columns.
- Repeating the last 3 steps for each relevant Column it will populate the left panel with various “Extra Tables” (D, INSTALL_YR, ID, L, MATERIAL in the example), one for each relevant Attribute.
The “Extra Tables” created have the Index field in common, and it will be used to Merge them together. The name of the colunms needs to be changed before hand to keep the information well organized. At this moment all tables has the values stored in colunms labeled as “Value.Value”. Next you will rename the “Value.Value” column using the appropriate name accordingle to the information stored: Diameter, Length, ID, etc.
- For each Extra Table, select the table and right click on the “Value.Value” column. Select Rename.
- Insert the name you desire.
- Repeat the Renaming for the other Extra Tables.
With the Value Column relabeled for each “Extra Table”, you can proceed with the Merging Procedure.
- Select Home > Merge Queries as New
- Select the First Extra Table in the Drop Menu, then the Second Extra Table in the other Drop down menu.
- Then Click in the “Index” column in the First Table, and in the Index Column for the Second Table. Follow the Picture below:
- Click Ok
It creates a New Query in the Left Panel. The Second Table appears initially with a grouped cell (ID column in the Picture below):
In the Next steps you will Merge the rest of the Extra Tables into the Table created by the Merge tool.
- Select the Table created by the Merge tool and Select Home > Merge Queries.
- Select the Third Extra Table and Select the Index field from the Merged Query and the Index field from the Third Extra Table. Follow the Picture below:
- Click Ok
- Merge the Other Extra Tables to the Merge Table on the same way.
Finally, we can Expand each column from the Extra Tables. Each group should have 3 attributes (Index, Value and Attribute)
- For each grouped column, click in the Separate button
- Keep Selected just the field that stores the Values.
- Disable the option to use the original column name as Prefix.
- Click Ok
- Repeat the Last Step for the Rest of the Columns. And it’s Done!
As a result you have an organized table with similar organization as seen in GIS platforms.
See Also
Creating and Publishing PowerBI reports
Automatically update of PowerBI reports into WaterSight
Accessing PowerBI reports in WaterSight