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.

IA data explorer link to ArcGIS button

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.

ArcGIS Online copy feature layer URL button

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


The URL should now look something like this:*&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.

Power BI URL box

You will get something like this:

Power BI data import

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

Power BI 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.

    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("*&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)
    #"Expanded Column1.attributes"

Power BI Advanced Editor new query

Click Done.

National Data Service data in Power BI

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.

Change data type in Power BI

Then click Close & Apply in the ribbon.

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

Power BI visualisation with National Data Service data