Hi everyone,
I have been solving the problem with invoking custom function. Always I got the error: Formula.Firewall: Query1 'Invoked Function' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Of course I have already read many articles (I recommended this by Chris Web) and shortly I understand why this is happening: in Power Query engine you cannot access two different data sources in one step.
But in my scenario I do not find solution how to do yet. In my scenario I am working with data from Google Analytics (but it is not important, generally you can work with SQL sources etc...)
My Scenario:
Query1: This query get all webs from Google Analytics and return only selected values (View_ID) as list (so Query1 result is list)
Query2: This query again get all webs from Google Analytics, but it is filtered by list Query1 (created in previous query). On the end of this query I would like to invoke all rows with function fnGetSessions.
fnGetSessions: This function call standard query to Google Analytics, where you can enter 3 parameters (Account_ID, Property_ID, View_ID). Also you can call function with parameters more than once (with multiple parameters).
But when the step invoked Custom Function in Query2 starts, the error will appear.
Query1
let
Source = GoogleAnalytics.Accounts(),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Id", "Name", "Data", "Kind"}, {"Data.Id", "Data.Name", "Data.Data", "Data.Kind"}),
#"Expanded Data.Data" = Table.ExpandTableColumn(#"Expanded Data", "Data.Data", {"Id", "Name", "Data", "Kind"}, {"Data.Data.Id", "Data.Data.Name", "Data.Data.Data", "Data.Data.Kind"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Data.Data",{{"Data.Id", "Property_ID"}, {"Data.Data.Id", "View_ID"}, {"Name", "Account"}, {"Id", "Account_ID"}, {"Data.Name", "Property_Name"}, {"Data.Data.Name", "View_Name"}}),
#"Filter by Property_Name" = Table.SelectRows(#"Renamed Columns", each [Property_Name] = "mysite.com"),
#"Convert to list" = #"Filter by Property_Name"[View_ID]
in
#"Convert to list" Query2
let
myList = Query1,
Source = GoogleAnalytics.Accounts(),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Id", "Name", "Data", "Kind"}, {"Data.Id", "Data.Name", "Data.Data", "Data.Kind"}),
#"Expanded Data.Data" = Table.ExpandTableColumn(#"Expanded Data", "Data.Data", {"Id", "Name", "Data", "Kind"}, {"Data.Data.Id", "Data.Data.Name", "Data.Data.Data", "Data.Data.Kind"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data.Data",{"Data.Data.Data", "Data.Data.Kind", "Data.Kind", "Kind"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Data.Id", "Property_ID"}, {"Data.Data.Id", "View_ID"}, {"Name", "Account"}, {"Id", "Account_ID"}, {"Data.Name", "Property_Name"}, {"Data.Data.Name", "View_Name"}}),
#"Filter by Query 1 values" = Table.SelectRows(#"Renamed Columns", each List.Contains(myList,[View_ID])),
#"Invoked Custom Function" = Table.AddColumn(#"Filter by Query 1 values", "fnGetSessions", each fnGetSessions([Account_ID], [Property_ID], [View_ID]))
in
#"Invoked Custom Function"
fnGetSessions
let Multiple = (Account_ID as text, Property_ID as text,View_ID as text)=>
let
Source = GoogleAnalytics.Accounts(),
#"Account" = Source{[Id=Account_ID]}[Data],
#"Property" = #"Account"{[Id=Property_ID]}[Data],
#"View" = #"Property"{[Id=View_ID]}[Data],
#"Added Items" = Cube.Transform(#"View",
{
{Cube.AddAndExpandDimensionColumn, "ga:isoYearIsoWeek", {"ga:isoYearIsoWeek"}, {"ISO Week of ISO Year"}},
{Cube.AddAndExpandDimensionColumn, "ga:channelGrouping", {"ga:channelGrouping"}, {"Default Channel Grouping"}},
{Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
})
in
#"Added Items" in Multiple
Of course you can do this without Query1, so you will need write the query like Query2 and filter Web properties again and again, but because I would prefer only one query for setup filter, so I am looking for solution.
Thanks,
Petr
