cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Data Table showing incorrect data (vs. Power Query & Google Analytics)

This issue is really causing me a headache. 

I'm pulling data from Google Analytics into my Power BI report. I have stripped this data down using:
Date

Sessions (I can strip this off, and still get same results)

Google Ads: Campaign

Revenue

Google Ads Campaign ID( I can strip this off, get same results)

 

The goal is to see campaign revenue by day, so I can filter specific campaigns with the later goal of linking these to other tables from my dataset, but for now, the problem lies in the initial data. 

Here's my problem:
I select a specific date to check the data, let's say 5/21

When I go to Google Analytics, I look at Campaign X, and let's say it generated $2,100 in revenue on this day

When I go to my Power Query and filter 5/21, it agrees and says we generated $2,100 with this campaign
When I Load the query into my report, and I go to the Data Table View and filter 5/21 and look at campaign X, it tells me campaign X generated $840 (it's about 40% less than the actual number). 

I have not yet made any data relationships, so it is a standalone table that I have not added anything to (no columns, measure, etc). 

 

I had this happen once before to me, and the fix was that I had data from 2012 onward, and when I filted my power query to show only data from 2017 onward, the query and the data table showed the same data. I did that same thing for this query, but it did not fix the discrepancy. 

 

Any help would be greatly appreciated! Thank you!

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: Data Table showing incorrect data (vs. Power Query & Google Analytics)

Hi @Hazenm 

Could you share screens here?

screens in Power Query, Data view?

Along with the code in Advanced editor?

 

Best Regards
Maggie

Highlighted
Frequent Visitor

Re: Data Table showing incorrect data (vs. Power Query & Google Analytics)

Yep! Sorry I should have done that to start. 

I was starting to post screen shots when I realized something, and maybe this is the key to figuring this out - when I filter the power query for a specific date, it gives me the right results, which I knew before. But I then saw that if I applied that filter to my dataset, when I go into the data table view, the data is correct. Again, removing the filter at the power query level, and applying, then filtering at the data table level, the data table is incorrect. 

Here is my advanced editor script:

----

let
Source = GoogleAnalytics.Accounts(),
#"3XXXX" = Source{[Id="3XXXX"]}[Data],
#"UA-3XXXX-1" = #"3XXXX"{[Id="UA-3XXXX-1"]}[Data],
#"4XXX" = #"UA-3XXXX-1"{[Id="4XXX"]}[Data],
#"Added Items" = Cube.Transform(#"4XXX",
{
{Cube.AddAndExpandDimensionColumn, "ga:adwordsCampaignID", {"ga:adwordsCampaignID"}, {"Google Ads Campaign ID"}},
{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
{Cube.AddAndExpandDimensionColumn, "ga:campaign", {"ga:campaign"}, {"Google Ads: Campaign"}},
{Cube.AddMeasureColumn, "Revenue", "ga:transactionRevenue"},
{Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each [Date] > #date(2017, 10, 30)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] = #date(2019, 5, 21)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Revenue", Order.Descending}})
in
#"Sorted Rows"

---

This is obviously with that filter on the power query level

First image is power query, second image is my Google Analytics view, Third image is the Data Table. So GA and Power query correct, Data table incorrect. 

image.pngimage.pngimage.png

 

Highlighted
Frequent Visitor

Re: Data Table showing incorrect data (vs. Power Query & Google Analytics)

@v-juanli-msft  any thoughts?

 

Highlighted
Community Support
Community Support

Re: Data Table showing incorrect data (vs. Power Query & Google Analytics)

Hi @Hazenm 

Sorry, i can't reproduce your problem on my side.

 

Best Regards
Maggie

 

Highlighted
Frequent Visitor

Re: Data Table showing incorrect data (vs. Power Query & Google Analytics)

I'm continuing to have this issue with Google Analytics data. I'm looking now at sessions: 

I can pull in my GA data, and look at:

1. Date

2. Default Channel Grouping

3. Transactions (# of transactions)

4. Sessions

 

and my data in my power query and my data table are correct

 

I then add:

5. Device Category

 

And now, my power query data is correct, but my data table/report data is wrong. 

 

Can anyone else offer some help/suggestions?

 

Thanks!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors