Forum Discussion

SimonKibsgaard's avatar
SimonKibsgaard
Helper I
9 years ago
Solved

Loading firebase database to Power BI

Hello

I have been searching all over to learn how to import Firebase JSON database into a Power BI table. Problem with most existing Q&A is that they all use key value pairs when selecting the json. Firebase uses nested objects. Still, I know my data well, so I hope someone could help me with the right selectors for the Query Editor.

Here's my data:

{
    "5d33cb83fd045541df50f14206f77212": {
        "691c32e30f0d255bf060007d91595e4b": {
            "-Kd0ZscC1DSkXg9Tl61w": {
                "date": "2017-02-15",
                "eatmyvegetables": "9",
                "postponeuntildeadline": "8",
                "r": "2f6ed490ddd72a0d354f03f8606e651f",
                "score": "27",
                "telltruthtospouse": "10"
            }
        },
        "ec1ba86ec0730d1234f84a979764a72f": {
            "-Kd0ZEiB6exMkmEmja7I": {
                "date": "2017-02-15",
                "eatmyvegetables": "6",
                "postponeuntildeadline": "8",
                "r": "49ff84fdb9794dc6290d632bc6f52ef1",
                "score": "23",
                "telltruthtospouse": "9"
            },
            "-Kd0ZePv8hCOj2YFpZNm": {
                "date": "2017-02-15",
                "eatmyvegetables": "5",
                "postponeuntildeadline": "8",
                "r": "006a1f199e397e818ac62b8ad7573a56",
                "score": "21",
                "telltruthtospouse": "8"
            }
        }
    },
    "feb27a647c4fec6d2b6890c2ffe75196": {
        "ae258795f6f053cfe8ea939baf37a20b": {
            "-Kd0_7e6Kg50Js1MjvUA": {
                "date": "2017-02-15",
                "eatmyvegetables": "9",
                "postponeuntildeadline": "8",
                "r": "4992a440952b4a58a1ef319895a2f7ac",
                "score": "23",
                "telltruthtospouse": "6"
            }
        },
        "afebba35074912de487f4bee43c09b7e": {
            "-Kd0FPHp6ONKKD-TMgZg": {
                "date": "2017-02-15",
                "eatmyvegetables": "8",
                "postponeuntildeadline": "7",
                "r": "bac16f8edfe59f53fcf9270b951fcd1e",
                "score": "18",
                "telltruthtospouse": "3"
            },
            "-Kd0G2W2hLMnZJ1vVJ1C": {
                "date": "2017-02-13",
                "eatmyvegetables": "8",
                "postponeuntildeadline": "7",
                "r": "bac16f8edfe59f53fcf9270b951fcd1e",
                "score": "18",
                "telltruthtospouse": "3"
            },
            "-Kd0HO8VVZooG8iy0r6N": {
                "date": "2017-02-13",
                "eatmyvegetables": "8",
                "postponeuntildeadline": "7",
                "r": "bac16f8edfe59f53fcf9270b951fcd1e",
                "score": "18",
                "telltruthtospouse": "3"
            }
        }
    }
}

And here is the result I am looking for (which also gives a better overview of the data itself):

https://drive.google.com/file/d/0B1bImHzfKJ60R0lOZGw1SnZQTEU/view?usp=sharing 

 

I know how to load the JSON, but not how to select nodes without knowing their names and to put parents and childrens into the same rows.

Hope it makes sense and I hope your are able to help me.

br, Simon

 

  • hugoberry's avatar
    hugoberry
    9 years ago

    Another approach is to use the function Record.ToTable(json) to drill down the json path. Please follow the query example below

     

    let
        json = Json.Document(File.Contents("C:\Users\cotrutai\Downloads\test.json")),
        level0 = Record.ToTable(json),
        level1 = Table.AddColumn(level0, "level1", each Record.ToTable([Value])),
        level1_exp = Table.ExpandTableColumn(level1, "level1", {"Name", "Value"}, {"level1.Name", "level1.Value"}),
        level2 = Table.AddColumn(level1_exp, "level2", each Record.ToTable([level1.Value])),
        level2_exp = Table.ExpandTableColumn(level2, "level2", {"Name", "Value"}, {"level2.Name", "level2.Value"}),
        out = Table.ExpandRecordColumn(level2_exp, "level2.Value", {"date", "eatmyvegetables", "postponeuntildeadline", "r", "score", "telltruthtospouse"}, {"date", "eatmyvegetables", "postponeuntildeadline", "r", "score", "telltruthtospouse"})
    in
        out

     

4 Replies

  • hugoberry's avatar
    hugoberry
    Responsive Resident
    9 years ago

    Here is a raw cut of what you can do. if json is the object containing the json you've listed in your question then you can get to the raw data via the following query:

     

    = Table.FromRecords(
                        List.Accumulate(List.Accumulate(Record.FieldValues(json)
                                                        ,{}
                                                        , (s,c)=>s&Record.FieldValues(c)
                                                        )
                                        ,{}
                                        ,(si,ci)=>si&Record.FieldValues(ci)
                                        )
                        )

     

    • hugoberry's avatar
      hugoberry
      Responsive Resident
      9 years ago

      Another approach is to use the function Record.ToTable(json) to drill down the json path. Please follow the query example below

       

      let
          json = Json.Document(File.Contents("C:\Users\cotrutai\Downloads\test.json")),
          level0 = Record.ToTable(json),
          level1 = Table.AddColumn(level0, "level1", each Record.ToTable([Value])),
          level1_exp = Table.ExpandTableColumn(level1, "level1", {"Name", "Value"}, {"level1.Name", "level1.Value"}),
          level2 = Table.AddColumn(level1_exp, "level2", each Record.ToTable([level1.Value])),
          level2_exp = Table.ExpandTableColumn(level2, "level2", {"Name", "Value"}, {"level2.Name", "level2.Value"}),
          out = Table.ExpandRecordColumn(level2_exp, "level2.Value", {"date", "eatmyvegetables", "postponeuntildeadline", "r", "score", "telltruthtospouse"}, {"date", "eatmyvegetables", "postponeuntildeadline", "r", "score", "telltruthtospouse"})
      in
          out