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

[nono_london]

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

[nono_london]

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]

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!

[nono_london]

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]

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