Reading Json text response from url with PowerBi Desktop-CodePudding

I am new to PowerBi. I would like to get a table on PowerIb, from this

Then I get a select between Html and Text which is fair:

And if I select Text, I get the text of the JSON, which is fair...

My question is how would I get the table associated with this JSON response. As well is it possible to skip the GUI and import the Json text in Python or JS, and deliver it to the GUI?

End result, would be a table with the items in the "data" key.

Thanks a lot, and best!

CodePudding user response：

Fundamentally, you are converting a plain JSON to a table. The JSON just happens to be sourced as plaintext from a URL.

JSON-to-table is well-explained in the docs - https://learn.microsoft.com/en-us/power-query/connectors/json.

Additionally, I highly recommend going through this excellent Power Query primer - https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let.

CodePudding user response：

I got an answer from another website and it worked:

Steps to follow:

from "New Source", select "Blank Query" (at the full bottom)
Inthe ribons, select "Advanced Editor"
Write "your code" in M (not easy...)

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

All done thanks to: ppm Tx a lot

Answer found on: this website