Forum Discussion

SimonKibsgaard's avatar
SimonKibsgaard
Icon for Helper I rankHelper 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....
  • 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

     

hugoberry's avatar
hugoberry
Icon for Responsive Resident rankResponsive 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

 

SimonKibsgaard's avatar
SimonKibsgaard
Icon for Helper I rankHelper I
9 years ago

That worked! Thanks hugoberry :-)