Loading a firebase database to excel with Power Query

[michaelbeeri]

I'm trying to load my Firebase data to Power BI, the data is a JSON map of the following format

{
    "soh4gf1k": {
        "name": "bill",
        "age": "40",
        "maritalStatus": "married"
    },
    "itnhwagx": {
        "name": "jane",
        "age": "28",
        "maritalStatus": "single"
    }
    ...
}

I tried to follow the instructions in Loading a json file into Power Query but in that case the data was in array form, and i can't seem to get it right for my case.

let
    Source = Json.Document(Web.Contents("url to firebase database")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"name", "age", "maritalStatus"})
in
    #"Expanded Value1"

throws the following error:

Expression.Error: We cannot convert a value of type Record to type Table. Details: Value=Record Type=Type

[michaelbeeri]

I'm trying to load my Firebase data to Power BI, the data is a JSON map of the following format

{
    "soh4gf1k": {
        "name": "bill",
        "age": "40",
        "maritalStatus": "married"
    },
    "itnhwagx": {
        "name": "jane",
        "age": "28",
        "maritalStatus": "single"
    }
    ...
}

I tried to follow the instructions in Loading a json file into Power Query but in that case the data was in array form, and i can't seem to get it right for my case.

let
    Source = Json.Document(Web.Contents("url to firebase database")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"name", "age", "maritalStatus"})
in
    #"Expanded Value1"

throws the following error:

Expression.Error: We cannot convert a value of type Record to type Table. Details: Value=Record Type=Type

[Anonymous]

Hi @michaelbeeri,

I make a test using your sample JSON data, and I get table as shown in the following screenshot, does it return your expected result?


If yes, please add the following codes in your Advanced Editor.

let
    Source = Json.Document(Web.Contents("url to firebase database")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"name", "age", "maritalStatus"}, {"Value.name", "Value.age", "Value.maritalStatus"})
in
    #"Expanded Value"

Thanks,
Lydia Zhang

[kiily]

Hi, I was wondering what firebase url you used for this? I keep being redirected to the Google login page. Any ideas?

[b0nesZA]

https://"Project".firebaseio.com/.json?print=pretty&auth="secret"

[bryangateley]

Hello I am getting the same error in power query. 

Why would it load in the previewer but not to the actual worksheet!!! 

 http://imgur.com/a/VVzVu

[hugoberry]

You are getting the error because the expansion works on the preview data which is limited to small subset. When you load the data to the worksheet, the epansion procedure runs over the entire dataset, and I'm sure there are some branches to this JSON that don't conform to the schema that you've showed in your example.

Have a look at other two forum questions that might guide you through troubleshooting the error.

http://community.powerbi.com/t5/Integrations-with-Files-and/Loading-firebase-database-to-Power-BI/m-...

http://community.powerbi.com/t5/Desktop/A-bit-more-advanced-JSON-to-Power-BI/m-p/130000/highlight/tr...

[bryangateley]

Hello I am getting the same error in power query. 

Why would it load in the previewer but not to the actual worksheet!!! 

 http://imgur.com/a/VVzVu