Forum Discussion

aurischa's avatar
aurischa
Regular Visitor
9 years ago
Solved

Multi-Dimensional Json file to Table

I have a Json file formatted like so: { "items": [ { "tableName": "incidents", "count": 20000, "columnNames": [ "id", ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi aurischa，

    Please follow the following steps to import your JSON file to Power BI.

    1. Right click your lists and choose "Add as new query".




    2. Convert your columnNames list to table and transpose the table.


    3.Convert your rows list to table and rename the column as follows


    4.Add custom columns as follows, For more details, please review this similar blog.



    5. Remove first column in rows table.


    6. Append the two queries as shown in the following screenshots.


    7. Select the first row in columnNames table and click the "Use first row as headers" button, then get expected result.
    .


    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks for taking your time looking into this. 

 

What I'm trying to do is to get some sense out of JSON-stat file from Eurostat (See here for the JSON-stat file and here for the table it self )

I've worked with JSON before and done some tricks here and there to create tables but this time I'm just stuck.

 

This is what I get from get data - Web - using this url: 

http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/tin00171?nace_r2=I551-I553&precision=1&lastTimePeriod=12&unit=NR&indic_to=B004&indic_to=B005&indic_to=B006

 

 

 

 

 

 

 

 

 

 

 

After using the Table.FromRecords function and removing some columns I don't need I get this

 

Result:

 

when I click on the "cells" get this from the lower pane view 

 

behind dimension recordsbehind value records

 

After going back and forth trying to attach the values to dimension I simply can't and that is possibly because of my lack of M-formula knowledge.

 

Is there a way I can get a table with the fields geo and time with correct values ? 

 

Kind regards and again, thanks for taking your time to look into this

ImkeF's avatar
ImkeF
Community Champion
8 years ago

So you want to link the "unit"-record to the number 2504985?

How? As a new record? Which field name shall the number get?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

     

    I basically just want the output to be tabular - like this 


     

     

     

     

     

     

     

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    I couldn't spot a key or pattern between these 2 items.

    So unfortunately I have no idea how to match them, sorry.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Oh well, thanks anyway : )