Loading firebase database to Power BI

[SimonKibsgaard]

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]

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)
                                    )
                    )

[SimonKibsgaard]

Hi @hugoberry, this really helped me. Unfortunately I also got my hands on some JSON a bit more advanced. I tried to follow your example, but wasn't able to extract. I have made a new Thread, so hopefully others can learn from it too. I would greatly appreciate it, if you would take a look at it: http://community.powerbi.com/t5/Desktop/A-bit-more-advanced-JSON-to-Power-BI/m-p/130000#U130000

br, Simon

[hugoberry · Accepted solution]

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

[SimonKibsgaard]

That worked! Thanks hugoberry 🙂