Power Query Editor - unexpected aggregation of columns

[Anonymous]

Hi there,

I have some trouble with M. Since I have tried all things I know to solve the problem, I would be happy if anyone else could give me some useful input.

My situation:

• I want to query multiple Google Analytics Accounts via the integrated Power BI connector
• All query results should be in one table
• I wrote a function (lets call it "GetDailyGoalConversions") which takes the basic analytics attributes (AccountID, PropertyID, Dataview) as input and then does all the table transformations I need and gives the resulting table as a output
• In a second step I do a query ("F02_Zielvorhaben") which runs the function for all accounts I want an combines the tables

So far so good.

My problem:

• If I run all steps of GetDailyGoalConversions seperatly in a single query all things work as expected and I get the result I need (but of course just for a single Account)
• As soon as I use the function there seems to appear a bug: The adding of dimension and metrics of google Analytics is working properly, but as soon as I insert a further step (it doesn't matter if it's just sorting by a column or doing a more complex transformation) Power BI aggregates some rows randomly which of course leads to wrong values of my conversion column (which is the only metric im trying to get)

Do you have any clue why this happens?

In the following I give you the code for the function GetDailyGoalConversions, the query F02_Zielvorhaben (Screenshot, due to data protection) and some screenshots which document the described behavior. Thanks for your help in advance!

GetDailyGoalConversion:

( 
    AnalyticsAccountID as text,  
    AnalyticsPropertyID as text,  
    AnalyticsDatensichtID as text,  
    partner as text,
    ZV as text,
    ZV_Name as text 

) as table =>

let
    Quelle = GoogleAnalytics.Accounts(),  
    #"AnalyticsAccountID" = Quelle{[Id = AnalyticsAccountID]}[Data],  
    #"AnalyticsPropertyID" = #"AnalyticsAccountID"{[Id = AnalyticsPropertyID]}[Data],  
    #"AnalyticsDatensichtID" = #"AnalyticsPropertyID"{[Id = AnalyticsDatensichtID]}[Data],  
    #"Hinzugefügte Elemente" = Cube.Transform(#"AnalyticsDatensichtID",
    {
            {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
            {Cube.AddAndExpandDimensionColumn, "ga:campaign", {"ga:campaign"}, {"Campaign"}},
            {Cube.AddAndExpandDimensionColumn, "ga:adwordsCampaignID", {"ga:adwordsCampaignID"}, {"Google Ads Campaign ID"}},
            {Cube.AddAndExpandDimensionColumn, "ga:source", {"ga:source"}, {"Source"}},
            {Cube.AddAndExpandDimensionColumn, "ga:medium", {"ga:medium"}, {"Medium"}},
            {Cube.AddMeasureColumn, ZV_Name, "ga:goal"&ZV&"Completions"}
        }),
    #"Entpivotierte Spalten" = Table.UnpivotOtherColumns(#"Hinzugefügte Elemente", {"Date", "Campaign", "Medium", "Source", "Google Ads Campaign ID"}, "NameZielvorhaben", "Conversions"),
    #"Gefilterte Zeilen2" = Table.SelectRows(#"Entpivotierte Spalten", each [Date] >= RangeStart ),
    #"Add Partner" = Table.AddColumn(#"Gefilterte Zeilen2", "Partner", each partner),
    #"Gefilterte Zeilen1" = Table.SelectRows(#"Add Partner", each ([Conversions] >= 1)),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Gefilterte Zeilen1",{{"Conversions", Int64.Type}}),
//Add Campaign ID
    #"Add Campaign ID" =
        Table.AddColumn(#"Geänderter Typ", "CampaignID", each
            if [Google Ads Campaign ID] = "(not set)" then Text.Combine({[Campaign],[Partner]}, "-")
            else [Google Ads Campaign ID]
        ),
//Nicht notwendige Spalten entfernen
    #"Spalten entfernen" =
        Table.RemoveColumns(#"Add Campaign ID",
            {
                "Google Ads Campaign ID",
                "Campaign"
            }
        ),
    #"Lowercase CampaignID" = Table.TransformColumns(#"Spalten entfernen",{{"CampaignID", Text.Lower, type text}})
in
    #"Lowercase CampaignID"

F02_Zielvorhaben:

If I run the function just until the step "Hinzugefügte Elemente" (this shows as example one goal of one account for one day):

The same account, the same day, the same goal - just adding another step to the function (in this case unpivoting the goal conversion column, but as mentioned earlier it doesn't matter which kind of transformation i do):

As you can see the sum of the conversion column changed from "3" (first screenshot) to "2" (second screenshot).
If I do the same transformation within a normal query everything works properly and it's all working as expected.

Thanks for your input and help in advance!

Cheers

[Anonymous · Accepted solution]

Meanwhile I found the solution. 

The reason for the bug: I queried "Campaign" AND "Google Ads Campaign ID". Both dimensions queried at the same time seem to force the explained bug in some (not all) cases. So the reason was to only query on the both dimensions.

I will mark this post as solved.