Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
juangbiestro
Helper I
Helper I

API JSON link issue

Hi guys,

 I have some problems with API from ABS web site as a data set on Power BI Desktop. I have read a lot and I can do with others samples API, but not with API from ABS. I dont have experience on Json, for this reason it is quite hard for me.

 I think the problem is probably the structure of the tables. Can you help me with this?.

this is the API data query: http://stat.data.abs.gov.au/sdmx-json/data/RES_PROP_INDEX/1+2+3.3+2+1.1GSYD+2GMEL+3GBRI+4GADE+5GPER+...

 

Cheers.

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

Hi @juangbiestro,

 

You should be able to use Get Data -> From Web and enter the URL (or local path) to the JSON file. PBI will interpret that file as JSON and show you the root node, which you can drill in and expand on to get to the data elements you want.

 

Here is a similar thread for your reference. The exact steps will depend on the shape of your JSON. For example, following is the sample query which I used to get the data from the "dataSets" elements of JSON file you proveded above.Smiley Happy

let
    Source = Web.Contents("http://stat.data.abs.gov.au/sdmx-json/data/RES_PROP_INDEX/1+2+3.3+2+1.1GSYD+2GMEL+3GBRI+4GADE+5GPER+6GHOB+7GDAR+8ACTE+100.Q/all?startTime=2014-Q4&endTime=2016-Q3&dimensionAtObservation=allDimensions"),
    #"2" = #table(1, {{Source}}),
    Column1 = #"2"{0}[Column1],
    #"Imported JSON" = Json.Document(Column1,65001),
    dataSets = #"Imported JSON"[dataSets],
    dataSets1 = dataSets{0},
    observations = dataSets1[observations],
    #"Converted to Table" = Record.ToTable(observations),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Value", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

dataset1.PNG

 

Regards

View solution in original post

2 REPLIES 2
v-ljerr-msft
Employee
Employee

Hi @juangbiestro,

 

You should be able to use Get Data -> From Web and enter the URL (or local path) to the JSON file. PBI will interpret that file as JSON and show you the root node, which you can drill in and expand on to get to the data elements you want.

 

Here is a similar thread for your reference. The exact steps will depend on the shape of your JSON. For example, following is the sample query which I used to get the data from the "dataSets" elements of JSON file you proveded above.Smiley Happy

let
    Source = Web.Contents("http://stat.data.abs.gov.au/sdmx-json/data/RES_PROP_INDEX/1+2+3.3+2+1.1GSYD+2GMEL+3GBRI+4GADE+5GPER+6GHOB+7GDAR+8ACTE+100.Q/all?startTime=2014-Q4&endTime=2016-Q3&dimensionAtObservation=allDimensions"),
    #"2" = #table(1, {{Source}}),
    Column1 = #"2"{0}[Column1],
    #"Imported JSON" = Json.Document(Column1,65001),
    dataSets = #"Imported JSON"[dataSets],
    dataSets1 = dataSets{0},
    observations = dataSets1[observations],
    #"Converted to Table" = Record.ToTable(observations),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Value", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

dataset1.PNG

 

Regards

Thanks for your help.

 

Cheers.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.