Forum Discussion

Hazenm's avatar
Hazenm
Advocate II
7 years ago

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 ge...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Hazenm 

Could you share screens here?

screens in Power Query, Data view?

Along with the code in Advanced editor?

 

Best Regards
Maggie

  • Hazenm's avatar
    Hazenm
    Advocate II
    7 years ago

    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. 

     

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Hazenm 

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

         

        Best Regards
        Maggie

         

    • ReportMaster's avatar
      ReportMaster
      Post Partisan
      2 years ago

      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:

       

       

      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:

       

       

      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.