InstantAtlas integration with Power BI

This article describes how to use data from the InstantAtlas National Data Service in Microsoft Power BI. The National Data Service is delivered as ArcGIS Online feature layers.

The steps for using data in ArcGIS in Power BI are described here. It is possible to use the steps in the Appendix to bring data from the National Data Service into Power BI.

However, when using data from the National Data Service specifically, a better result can be achieved using a custom query in the Power BI Advanced Editor. The steps for doing this are described below.

Sign in to the Data Catalog Hub and use the Data Explorer app to search for the indicator you are interested in. Click on any of the tabs and click the button to view the item in ArcGIS Online.

In ArcGIS Online, on the item overview page, click the layer corresponding to the geography you want to display data for in Power BI. Then click the Copy button on the right to copy the URL.

Amend this URL (e.g. in Notepad) to add this to the end:


query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json

The URL should now look something like this:


https://services1.arcgis.com/HumUw0sDQHwJuboT/arcgis/rest/services/Suffolk_Pop_Est_Female/FeatureServer/0/query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json

Open Power BI Desktop and click Get Data, Web. Paste your URL into the URL box and click OK.

You will get something like this:

Click Transform Data. The Power Query Editor will open. Click Advanced Editor.

Replace the query in the Advanced Editor with the one below. Make sure you adjust the URL at the start of the query (shown in red) to the one you are actually using.


let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://services1.arcgis.com/HumUw0sDQHwJuboT/arcgis/rest/services/Suffolk_Pop_Est_Female/FeatureServer/0/query?where=1%3D1&outFields=*&returnGeometry=false&resultRecordCount=0&f=json"), null, null, 65001)}),
    #"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),
    Column1 = #"Parsed JSON"{0}[Column1],
    fieldSet = Column1[fields],
    fieldNames = List.Transform(fieldSet, each Record.Field(_, "name")),
    fieldAliases = List.Transform(fieldSet, each Text.Combine({Text.Replace(Record.Field(_, "alias"), "|", " | "), " (", Record.Field(_, "name"),")"})),
    features = Column1[features],
    #"Converted to Table" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"attributes"}, {"Column1.attributes"}),
    #"Expanded Column1.attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attributes", fieldNames, fieldAliases)
in
    #"Expanded Column1.attributes"

Click Done.

Note that the data type for the columns is text. You should change this for the data you wish to use. Select the columns in question, right-click, Change Type and choose the appropriate type.

Then click Close & Apply in the ribbon.

You can then create your visualisation with the data from the feature layer.

InstantAtlas Help