Need help connecting PBI to public government data

[arpost]

I have a couple of government datasets to which I want to connect Power BI directly:

https://data.cms.gov/provider-data/dataset/y9us-9xdf

https://data.cms.gov/provider-data/dataset/dgck-syfz

These both offer APIs on the pages above, so I thought I could just connect using PBI's Web connector, but I can't seem to work out how to actually pull the data.

Anyone able to offer some insight?

[v-shex-msft]

Hi @arpost,

Did lbendlin 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information about your requirements to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[lbendlin · Accepted solution]

something like this

let
    Source = Json.Document(Web.Contents("https://data.cms.gov/provider-data/api/1/metastore/schemas/dataset/items/y9us-9xdf?show_reference_ids=")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded contactPoint" = Table.ExpandRecordColumn(#"Converted to Table", "contactPoint", {"@type", "fn", "hasURL"}, {"contactPoint.@type", "contactPoint.fn", "contactPoint.hasURL"}),
    #"Expanded publisher" = Table.ExpandRecordColumn(#"Expanded contactPoint", "publisher", {"identifier", "data"}, {"publisher.identifier", "publisher.data"}),
    #"Expanded publisher.data" = Table.ExpandRecordColumn(#"Expanded publisher", "publisher.data", {"@type", "name"}, {"publisher.data.@type", "publisher.data.name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded publisher.data",{{"accessLevel", type text}, {"landingPage", type text}, {"bureauCode", type any}, {"issued", type date}, {"@type", type text}, {"modified", type date}, {"released", type date}, {"keyword", type any}, {"contactPoint.@type", type text}, {"contactPoint.fn", type text}, {"contactPoint.hasURL", type text}, {"publisher.identifier", type text}, {"publisher.data.@type", type text}, {"publisher.data.name", type text}, {"identifier", type text}, {"description", type text}, {"title", type text}, {"programCode", type any}, {"distribution", type any}, {"theme", type any}, {"%modified", type date}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".