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

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