Convert json List Record to Table value

[Jayendran]

Hi,

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.

This is my JSON File

{
    "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 want this to be a table like 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 getting error like Expression.Error: We cannot convert a value of type Record to type

Please help me to get solve this

Thanks !

Jay

[v-jiascu-msft]

Hi Jay,

Since you have a standard JSON file, I would suggest you use the built-in connector JSON and follow the steps one by one. The underlying code will be generated by the Query Editor automatically.

Best Regards,

[Zubair_Muhammad · Accepted solution]

@Jayendran 

You can use this

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"

Please see attached files as well

[Jayendran]

Thanks for the solution, Actually I'm missing the ExpandRecordColumn Function