Forum Discussion

dinosainsburys's avatar
dinosainsburys
Helper I
2 years ago
Solved

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

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.

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.