How do I just extract the raw JSON data pulled from an API?

[dinosainsburys]

I pull in data from an API query which is in a JSON format. It formats most of the stuff very nicely but there are some columns which have further tables and tables nested inside of them. I simply just want to extract the raw JSON format coming into these tables and I can do my own text transformations as needed.

[Anonymous]

Hi, @dinosainsburys 

You can write M code like this:

let
    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/reports/reportId", [Headers=[Authorization="Bearer token]])),
    #"Converted to Table" = Record.ToTable(Source)
   
in 
 #"Converted to Table"

This will return all the contents of the corresponding json, as shown in the following image:

In Power Query, the following image is shown:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dinosainsburys]

How do I do this just for two specific columns?

[Anonymous · Accepted solution]

Hi, @dinosainsburys 

Thank you very much for your reply. If you want to add these two columns to the original query, you need the Table.AddColumn function. 

In the source query, my query looks like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XXXXXXXXX=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}})
in
    #"Changed Type"

Now, I want to add my query above to this query, so the M code might look something like this:

let
    // Original table query
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XXXXXXXXX=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source1,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),

    // Additional query
    Source2 = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/reports/reportId", [Headers=[Authorization="Bearer token]])),
    #"Converted to Table" = Record.ToTable(Source2),

    // Add columns from the additional query to the original table
    #"Added Columns" = Table.AddColumn(#"Changed Type", "NewColumn1", each #"Converted to Table"[Value]{0}), // Add first column
    #"Added Columns2" = Table.AddColumn(#"Added Columns", "NewColumn2", each #"Converted to Table"[Value]{1}) // Add second column
in
    #"Added Columns2"

Here are the results:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.