Hi all!
I'm attempting to use Power BI to feed in Google Analytics data. One GA account can have multiple websites, and it's difficult to access all of these in one go in Power BI! I've been following a pretty poorly written blog post which almost works until the end.
I was hoping to get some help working out what exactly is going wrong!
Below are the steps I'm following from the blog post, but made more concise.
First, create Google analytics query
1. Open Power BI blank report
2. Select Google Analytics as data source
3. Select one of your sites, and the attributes/ measures you want, import
Next, recover the 3 IDs for your specific sites, by creating a table with all sites
1. Create a blank query
2. Advance editor
3. Insert below code:
let
Source = GoogleAnalytics.Accounts()
in
Source
4. Expand the table twice to get all data
5. Rename the 3 IDs as in the screenshot here
Go back to first query
We have to convert the first query to a function so that it dynamically gets the 3 IDs for each site
1. Select the query and go to the advance editor
2. Before the let, insert:
let Multiple =(id1 as text,id2 as text,id3 as text)=>
3. And after the query, insert:
In Multiple
4. “Replace the 3 ids in this query with “id1”, “id2” and “id3”
5. As in this screenshot
6. This should convert the table into a function
7. “If you want you can try it: insert the 3 IDs of your other table (the query with sites list) into the function and execute it. The query show your statistics about your site.”
THIS PART DOESN'T WORK! I fill in the parameters for the function (screenshot), but I get an error (screenshot)
Now go back to your sites list query
1. Create a new column, where “Nameofyourfunction” is the name of your function
=Nameofyourfunction([Id],[2ID],[3ID])
2. Expand the new column
THIS PART DOESN'T WORK! When I expand the column, I only get 11 rows returned, with a red dashed line of doom at the top! [Screenshot]
And the FINAL error: when I click apply, I get the following error code which says "OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type Text..
Any help would be greatly appreciated, I've been trying to work this out for days to no avail!
