Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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)
)
)
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
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
That worked! Thanks hugoberry 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.