cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate IV
Advocate IV

JSON from URL in Table Column?

I have simple 2 column table with an ID and URL. Basic structure of URL parts are:

JSON_Column.png

Pasting one of the URLs into web browser does show data formatted as JSON and I can view the record(s) when creating a new query using Json.Document(Web.Contents("url")).

 

What I'm HOPING to acomplish is convert URL column to list of JSON record(s) associated with each ID. I've tried various combinations of column transfomations (Json.Document, Web.Contents, List.Transform, etc.), but am getting the dreaded "Formula.Firewall" error.

 

I know it's doable, but apparently my brain is still in quarantine.

 

TIA, Danny

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: JSON from URL in Table Column?

Hi @musicbydannyd ,

 

Define a variable and then invoke custom function.

M code may like this:

(id as number) as table=>
let
    Source = Table.FromRecords(Json.Document(Web.Contents("https://xxxxxxxx" &Number.ToText(id)&"xxxx"))[subjects])
in
    Source

test_JSON from URL in Table Column..PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

Re: JSON from URL in Table Column?

Obviously this depends on the format of the actual JSON behind the URLs but you should just be able to expand the records that are returned from each Json.Document(Web.Contents(JSON))

 

Maybe the column name JSON throws Power Query off?

Highlighted
Microsoft
Microsoft

Re: JSON from URL in Table Column?

Hi @musicbydannyd ,

 

Define a variable and then invoke custom function.

M code may like this:

(id as number) as table=>
let
    Source = Table.FromRecords(Json.Document(Web.Contents("https://xxxxxxxx" &Number.ToText(id)&"xxxx"))[subjects])
in
    Source

test_JSON from URL in Table Column..PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Advocate IV
Advocate IV

Re: JSON from URL in Table Column?

@V-lianl-msft, I was afraid there might be a custom function involved for this rookie... appreciate the example! 😁

 

Let me do a little more research and I'll report back with a solution or more help.

 

Thanks again all!

DannyD

Highlighted
Advocate IV
Advocate IV

Re: JSON from URL in Table Column?

@V-lianl-msft, unfortunately, I had to put this project on hold for a while. During my last attempt, I WAS able to successfully create a formula as you described, however was still getting the "Formula.Firewall" error when invoking. Thanks, Danny

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors