Forum Discussion

Jayendran's avatar
Jayendran
Solution Sage
7 years ago
Solved

Convert json List Record to Table value

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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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's avatar
      Jayendran
      Solution Sage
      7 years ago

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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,