Forum Discussion
Helper IGoogle Analytics goal completions data inconsistency
Hi Jimmy801 thank you very much for the reply!
I contacted Power BI support about this and their response, combined with yours, explains what's going on.
The TLDR solution is:
- Get your request working in the Google Analytics Query Explorer with the appropriate date range filters and make sure it says the data is not sampled.
- Apply the same date range filters via a Power Query filter step, and the data should match the query explorer.
For anyone else who runs into this problem, the issue has to do with date range filtering (or lack thereof) with the Google Analytics API. By default, Power BI doesn't pass any date filtering parameters when it makes the API request, and that's what results in inconsistent data — in my testing, it can actually be sampled data even when you're not working with big numbers, I guess because it's asking Google Analytics to return data for potentially years worth of data without any date filtering.
The key is to add a filter step in Power Query that limits the date range, which somehow makes its way into the resulting API request (I guess through the MDX that you mentioned, which is a concept I was not familiar with).
I also found it very useful to try the same request in the Google Analytics Query Explorer to see how different filtering parameters (often larger date ranges) result in the tool telling you whether the returned data is sampled or not.
Community ChampionHello danxshap
try to add a Table.Buffer before your sorting step. This should fix the issue
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Helper IHi Jimmy801,
Thank you for the reply! I just tried that but it didn't change the results.
Here's the buffer step I added:
And here's the resulting data after I sort it so I can see recent months:
To rule out the possibility that the sorting step is somehow messing up the data, I checked the example row even without sorting and it still has the wrong value:
Any other ideas on how I could fix this?
Thank you!
- Jimmy801
Hello danxshap
I saw that Power Query is using cube-functions here. Meaning that in the background an MDX is created to give you a result. It's difficult to analyse / understand why on a selectrows the result it's okay. So you would need to understand what this functions is changing in the query made from Power Query to Google. Maybe there are happing some groupings. You could try to apply a grouping to the first two columns and appliying a sum-function to your measure column (not applying Table.Buffer anymore) or to write a MDX/DAX? on your own.
Check out this possibilities and let me know if this changed something.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy- danxshap
Hi Jimmy801 thank you very much for the reply!
I contacted Power BI support about this and their response, combined with yours, explains what's going on.
The TLDR solution is:
- Get your request working in the Google Analytics Query Explorer with the appropriate date range filters and make sure it says the data is not sampled.
- Apply the same date range filters via a Power Query filter step, and the data should match the query explorer.
For anyone else who runs into this problem, the issue has to do with date range filtering (or lack thereof) with the Google Analytics API. By default, Power BI doesn't pass any date filtering parameters when it makes the API request, and that's what results in inconsistent data — in my testing, it can actually be sampled data even when you're not working with big numbers, I guess because it's asking Google Analytics to return data for potentially years worth of data without any date filtering.
The key is to add a filter step in Power Query that limits the date range, which somehow makes its way into the resulting API request (I guess through the MDX that you mentioned, which is a concept I was not familiar with).
I also found it very useful to try the same request in the Google Analytics Query Explorer to see how different filtering parameters (often larger date ranges) result in the tool telling you whether the returned data is sampled or not.