Skip to main content
cancel
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

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