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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

Getting a table from a Json text response url with PowerBi Desktop API

I am new to PowerBi and would like to receive a table from a JSON URL:

* the url is : Data from API 

* sample data looks like this (one formatted-not with PowerBi):


{"data": [
{"user_id": 54710, "hp_user_id": 5806514, "username": "Jay_J1",
"user_profile_url": "",
"user_blocked": 0, "hp_post_id": 8550808,
"post_datetime": "2022-11-28 10:54:00",
"post_url": "", "post_summary": "Like many others have experienced, the hinge on my HP Envy 360 broke last night. All I did was simply open it, but I heard a crack, and now the metal part is stuck at an angle to where I can no l...", "me_too": "", "post_tags": "\"[\"HP ENVY x360 Laptop - 15m-ee0023dx\",\"Microsoft Windows 11\"]\""}, {"user_id": 52629, "hp_user_id": 5800465, "username": "BrookeDorbit", "user_profile_url": "", "user_blocked": 0, "hp_post_id": 8550124, "post_datetime": "2022-11-27 15:39:00", "post_url": "", "post_summary": "I\u2019ve seen many others mention the same issue with their HP envy laptop but I am just as upset. I purchased my laptop in the summer of 2020 and only 2 years later, the hinge is broken. I have never o...", "me_too": "\"[{\"username\":\"Jay_J1\",\"hp_user_id\":\"5806514\",\"post_datetime\":\"2022-11-28\"}]\"", "post_tags": "\"[\"HP ENVY Laptop - 13t-ba000 CTO\"]\""

So far I tried to get the data from JSON and Web, but the way data are formatted (in plain text) is being rendered in plain text.

In Python I can use dumps or loads to turn a string into a dict.

How can I achieve this in PowerBi?

Tx a lot for the help

New Member

Tx a lot for the help, got it working tx to you, and thank for teaching me this blank call.


working code is slithly different, I guess API call changed:



= Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"user_id", "hp_user_id", "username", "user_profile_url", "user_blocked", "hp_post_id", "post_datetime", "post_url", "post_summary", "me_too", "post_tags"}, {"user_id", "hp_user_id", "username", "user_profile_url", "user_blocked", "hp_post_id", "post_datetime", "post_url", "post_summary", "me_too", "post_tags"})






New Member

I get this (found the blank query)...


sorry if I am slow, and more importantly, I can see that you have managed to make it work!

New Member

Hi Pat, tx a lot for the answer.

How to I access to a blanck query? 

In case this is relevant, I do not have the Sign In access, just Desktop Bi usage.

tx a lot for the help

Solution Sage
Solution Sage

Weird that the defaults with the Web connector didn't work, but pls see this M code on how to extract the data. Create a blank query in the editor, open Advanced Editor and replace the M code there with this.

    Source = Json.Document(Web.Contents("")),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"user_id", "hp_user_id", "username", "user_profile_url", "user_blocked", "hp_post_id", "post_datetime", "post_url", "post_summary", "me_too", "post_tags"}, {"user_id", "hp_user_id", "username", "user_profile_url", "user_blocked", "hp_post_id", "post_datetime", "post_url", "post_summary", "me_too", "post_tags"})
   #"Expanded Column1"





Microsoft Employee

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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