cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Invoking Custom function with multiple parameters from another query

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Invoking Custom function with multiple parameters from another query

Hi PetrP,

 

This may caused by another data source has been imported in custom function, staging query should be seperated with shaping query like diagram below:

SNAGHTML409dfd44_thumb.png 

 

Reference: 

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-or-steps-so...

 

Regards,

Jimmy Tao

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Invoking Custom function with multiple parameters from another query

Hi PetrP,

 

This may caused by another data source has been imported in custom function, staging query should be seperated with shaping query like diagram below:

SNAGHTML409dfd44_thumb.png 

 

Reference: 

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-or-steps-so...

 

Regards,

Jimmy Tao

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors