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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
nono_london
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": "https://h30434.www3.hp.com/t5/user/viewprofilepage/user-id/5806514",
"user_blocked": 0, "hp_post_id": 8550808,
"post_datetime": "2022-11-28 10:54:00",
"post_url": "https://h30434.www3.hp.com/t5/Notebook-Hardware-and-Upgrade-Questions/HP-Envy-360-broken-left-hinge-and-screen-separating/m-p/8550808?search-action-id=587041408161&search-result-uid=8550808", "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": "https://h30434.www3.hp.com/t5/user/viewprofilepage/user-id/5800465", "user_blocked": 0, "hp_post_id": 8550124, "post_datetime": "2022-11-27 15:39:00", "post_url": "https://h30434.www3.hp.com/t5/Notebook-Hardware-and-Upgrade-Questions/Hinge-Issue/m-p/8550124?search-action-id=586827468132&search-result-uid=8550124", "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

4 REPLIES 4
nono_london
New Member

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

Best

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"})

 

 

```

 

 

nono_london
New Member

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

Capture.PNG

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

nono_london
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

ppm1
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.

let
    Source = Json.Document(Web.Contents("https://hptrial.pythonanywhere.com/rest_api_data")),
    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"})
   
in
   #"Expanded Column1"

ppm1_0-1669771281955.png

 

Pat

 

Microsoft Employee

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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