Dynamically Generating Tables from AdobeAnalytics API within Power BI Desktop - Power Query (M)

[Anonymous]

Hello Power Query (M) and Power BI Gurus!

I'm trying to use 3 Lists within Power BI Desktop as my Parameters to create tables using the AdobeAnalytics Connector (API) (See Below for the generated Power Query Code). The AdobeAnalytics Connector only allows for the use of Segments stacked on top of each other (For those that are not familiar with AdobeAnalytics think of a segment as a filter). So my only option (that I see thus far) is to iterate through all possible combinations of the 3 lists to generate all of the calls (i.e. trying to a Nested Loop that creates all of these tables for me and then combines them to one table).

Data Components:

• 3 Lists of values to use as my Nested Parameters for each call

  • Programs (1 Column)

    • A
    • B
    • C

  • Regions (1 Column)

    • A1
    • B2
    • C3

  • MarketingChannels (1 Column)

    • A11
    • B22
    • C33

• Power BI Desktop - Power Query AdobeAnalytics API connection

Required Steps

1. Create a table for every combination of the Values within the 3 Lists e.g.:

• Table 1 would use the following parameters:
  • (#Programs[A], #Regions[A1], #MarketingChannels[A11])
• Table 2 would use the following parameters:
  • (#Programs[A], #Regions[A1], #MarketingChannels[B22])
• Continue until all possible combinations are completed

2. Once all of the tables have been created I need to combine them

The below code snippet would be an example of Table 1,

(note the line that starts with "{Cube.ApplyParameter, "Segment","as that is where the Lists above would be used as parameters)

let
    Source = AdobeAnalytics.Cubes(),
    invitrogenprod = Source{[Id="invitrogenprod"]}[Data],
    #"Added Items" = Cube.Transform(invitrogenprod,
        {
            {Cube.ApplyParameter, "DateRange", {#date(2018, 3, 21), #date(2018, 3, 21)}},

            {Cube.ApplyParameter, "Segment", {{"#Programs[A]", "#Regions[A1]", "#MarketingChannels[A11]"}}},

            {Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month", "day"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day"}},
            {Cube.AddMeasureColumn, "Engaged Visits", "cm300001617_5a84e003de3c977de97c39d2"},
            {Cube.AddMeasureColumn, "Engaged Visits (PDP)", "cm300001617_5a58160e8f0c657d68a6e404"},
            {Cube.AddMeasureColumn, "Visits w/ Cart Activity", "cm300001617_5a50160c4ecbca5712928ab6"}
        })
in
    #"Added Items"

Any and all help will be greatly appreciated!!!

[v-yuezhe-msft]

@Anonymous,

A method I can think of is to create parameters(e.g, program parameter,regions parameter and marketing parameter) that contained the following values in Power BI Desktop, then use these parameters into your Power Query code.


(#Programs[A], #Regions[A1], #MarketingChannels[A11])

(#Programs[A], #Regions[A1], #MarketingChannels[B22])

There is a blog for your reference:
http://biinsight.com/power-bi-desktop-query-parameters-part-1/

Regards,
Lydia

[Anonymous]

Thank you for the response Lydia! I was already aware of and am using parameters but I need to not just print the parameters but run the same query for all possible combinations of those parameters in the query I listed in the original post (I have already added in parameters to where they would need to be placed).

[v-yuezhe-msft]

@Anonymous,

Do you list all possible values when creating parameters? If you have, you are able to choose values from drop-down list.


Regards,
Lydia

[Anonymous]

Thank you again for your response! I need to run this for every possible combination of the list of values. So need less to say that would be a bit time consuming if I were to have to choose the combination Everytime.

[v-yuezhe-msft · Accepted solution]

@Anonymous，

How about creating a single parameter that listing all the possible combination values, then using this single paramater in your M script?

Regards,
Lydia