CODEWITHSUNDEEP
jsonpowerbipowerbi-desktop
Jayendran
Jayendran

Reputation: 10960

Convert json List Record to Table value in PowerBI

I'm totally new to Power BI so I'm tried different approached to convert my JSON file to a table but unsuccessful so far.

{
    "Family": [
        {
            "Father": "F1",
            "Age": 50,
            "Mother": "M1",
            "MAge": 49,
            "Children": [
                {
                    "Name": "C1"
                },
                {
                    "Name": "C2"
                }
            ]
        },
        {
            "Father": "F2",
            "Age": 55,
            "Mother": "M2",
            "MAge": 53,
            "Children": [
                {
                    "Name": "Cc1"
                },
                {
                    "Name": "Cc2"
                }
            ]
        }
    ]
}

I'm trying to convert this into the table below

Father    Age      Mother    MAge
F1         50        M1        49

F2         55        M2        53

I tried like convert table and transpose which is not working I always get an error like

Expression.Error: We cannot convert a value of type Record to type

Upvotes: 8

Views: 43532

Answers (3)

Reza Aghaei
Reza Aghaei

Reputation: 125322

You can do it using only the web/desktop interface:

  1. Open Power BI Desktop

  2. Get Data → More ... → Select JSON from the list and click on Connect (You can also use WEB API or other sources which gives you JSON data)

  3. Choose the JSON file and open it.

  4. In the data panel, you see Family|List. Click on List link to add a navigation step.

  5. From Transform tab, click on To Table and from dialog click on OK.

  6. From the header of "Column1", click on Expand columns button expand columns to expand columns and from the menu, uncheck Use original column name as prefix and check the columns that you want and click on OK.

  7. You will see the columns and data in table format. You can change data type of columns by click on data type button change data type.

And finally you will have something like this:

power bi convert json data to table

Finally if you want to see the generated query, click on Advanced Editor button and see the code:

let
    Source = Json.Document(File.Contents("C:\Users\rag\Desktop\data.json")),
    Family = Source[Family],
    #"Converted to Table" = Table.FromList(Family, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Father", "Age", "Mother", "MAge"}, {"Father", "Age", "Mother", "MAge"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Age", type number}, {"MAge", type number}})
in
    #"Changed Type"

More information:

Upvotes: 16

Sattyajit Saha
Sattyajit Saha

Reputation: 11

I just followed this article to get data from a JSON file. When I clicked the Transform button, the Power Query engine automatically show me the final table. So I think Now Power bi(latest version 2.107. 683.0) has auto capability features to do this job for us. Thanks.

enter image description here

Upvotes: -2

Jayendran
Jayendran

Reputation: 10960

I'm missing the step of ExpandRecordColumn Function. After I put that in my Query. It's worked.

let
    Source = Json.Document(File.Contents("C:\Users\hp\Desktop\File.JSON")),
    Family = Source[Family],
    #"Converted to Table" = Table.FromList(Family, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Father", "Age", "Mother", "MAge", "Children"}, {"Father", "Age", "Mother", "MAge", "Children"})
in
    #"Expanded Column1"

Upvotes: 5

Related Questions