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

Answer 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.

Answer 2

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