Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
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"})
```
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!
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
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"
Pat
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
84 | |
75 | |
68 | |
41 | |
35 |
User | Count |
---|---|
107 | |
56 | |
52 | |
48 | |
40 |