Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hazenm
Advocate II
Advocate II

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!

6 REPLIES 6
Hazenm
Advocate II
Advocate II

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!

v-juanli-msft
Community Support
Community Support

Hi @Hazenm 

Could you share screens here?

screens in Power Query, Data view?

Along with the code in Advanced editor?

 

Best Regards
Maggie

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

 

Hi @Hazenm I know this reply is very late, but were you able to find a solution? I used the GA native connector but had some issues with my data sometimes. As a workardoun, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supemetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the GA connector in the data sources list:

 

GA-1.png

 

After that, just grant access to your GA account using your credentials, then on preview and destination page you will see a preview of your GA fields:

 

GA-2.png

 

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.

 

SELECT_DESTINATION_NEW.png

@v-juanli-msft  any thoughts?

 

Hi @Hazenm 

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

 

Best Regards
Maggie

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.