Querying BigQuery Events data in PowerBI

Question

Hi I have analytics events data moved from firebase to BigQuery and need to create visualization in PowerBI using that BigQuery dataset. I'm able to access the dataset in PowerBI but some fields are in array type I generally use UNNEST while querying in console but how to run the query inside PowerBI. Is there any other option available? Thanks. Table In BigQuery

could you show your table, mostly in PowerBI it is easily expandable. — AnkUser, Aug 29 '19 at 09:33

score 1 · Accepted Answer · answered Aug 30 '19 at 07:01

1

What we did until the driver fully supports arrays is to flatten in a view: create a view in bigquery with UNNEST() and query that in PBI instead.

answered Aug 30 '19 at 07:01

yan-hic

1,374
11
26

Thanks Yannick but can we create dynamic view on BigQuery? – SaiSivaSubramanian L Sep 04 '19 at 07:50

score -1 · Answer 2 · answered Aug 29 '19 at 11:10

You might need to Transform(parse Json into columns/rows) your specific column in your case event_params

So I have below Json as example for you.

{
    "quiz": {
        "sport": {
            "q1": {
                "question": "Which one is correct team name in NBA?",
                "options": [
                    "New York Bulls",
                    "Los Angeles Kings",
                    "Golden State Warriros",
                    "Huston Rocket"
                ],
                "answer": "Huston Rocket"
            }
        },
        "maths": {
            "q1": {
                "question": "5 + 7 = ?",
                "options": [
                    "10",
                    "11",
                    "12",
                    "13"
                ],
                "answer": "12"
            },
            "q2": {
                "question": "12 - 8 = ?",
                "options": [
                    "1",
                    "2",
                    "3",
                    "4"
                ],
                "answer": "4"
            }
        }
    }
}

I had this json added to my table. currently it has only 1 column

Now I go to Edit queries and go on Transform Tab, there you find Parse, In my case I have Json

When you parse as Json you will have expandable column

Now click on expanding it and sometimes it asks for expand to new row.

Finally you will have such a Table

BigQuery ODBC driver does not pass schema details for ARRAY of RECORD. Also a JSON transform is slow in PBI — yan-hic, Aug 30 '19 at 06:58
just a question, does it mean we cannot transform array of records into columns/rows just like we transform Json? — AnkUser, Aug 30 '19 at 07:05
That's correct because the JSON returned by the Simba drivers (ODBC/JDBC) do not include the actual field names, only dummy `v` or `f` — yan-hic, Aug 30 '19 at 08:28

Querying BigQuery Events data in PowerBI

2 Answers2