locked
Combine data sources - Firewall issue RRS feed

  • Question

  • Hi,

    i have used the great article of Ken Puls (https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/#comment-292632) till now but came to a situation where i am stuck,

    so i created a simple example and detailed it up (code is at the end of the details)
    i have a table in excel that holds parameters
    and a function (GetParam) that for GetParam("Date") would return #date(2017,2,1)

    i have two simple staging queries (StagingQry1, StagingQry2) that uses the parameter to take data from the SQL database

    now all that i need is a query that would merge StagingQry1 with StagingQry2 and would filter the data there after using the parameter in the sheet (Result1)

    what i do not understand is why or how "Flt" in query Result1 i am receiving
    "Formula.Firewall: Query 'Result1' (step 'Flt') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

    i would appreciate if you can let me know what have i done wrong, and how (if possible) would i be able to fix it without changing the privacy level

    Code:

    Function GetParam
    (ParamName as text) =>
    let
    ParamSource = Excel.CurrentWorkbook(){[Name="ParamTable"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each([Nm]=ParamName)),
    Value=if Table.IsEmpty(ParamRow)=true then null else Record.Field(ParamRow{0}, "Val")
    in
    Value

    StagingQry1
    let
    Source = Sql.Database("Srv1\Sql_2014", "DB2", [Query="select * from table1 where td>='" & DateTime.ToText(GetParam("Date"), "yyyyMMdd") & "'" ])
    in
    Source

    StagingQry2
    let
    Source = Sql.Database("Srv2\Sql_2014", "DB1", [Query="select * from table2 where createdate>='" & DateTime.ToText(GetParam("Date"), "yyyyMMdd") & "'" ])
    in
    Source

    Result1
    let
    Source1 = TCQry,
    MrgQry = Table.NestedJoin(StagingQry1,{"Id"},StagingQry2,{"Id"},"Qry",JoinKind.LeftOuter),
    Flt = Table.SelectRows(MrgQry , each [td]=GetParam("Date"))
    in
    Flt

    Regards,

    Ronen

    Monday, November 6, 2017 9:05 AM

Answers

  • Hi Ronen,

    The issue is caused by the fact that you're doing a merge (Table.NestedJoin) in the same query as a data source access (GetParam("Date"), which accesses the current workbook).

    You can resolve the issue by extracting the data source access into a separate query.

    DateParam
    = GetParam("Date")

    Then reference this new query from Result1. (However, you'll still want to use the function directly in your staging queries, since those are accessing data sources.)

    Result1

    let
    Source = Table.Buffer(Table.NestedJoin(StagingQry1,{"ProductID"},StagingQry2,{"ProductID"},"Qry",JoinKind.LeftOuter)),
    Flt = Table.SelectRows(Source, each [SellStartDate]=DateParam)
    in
    Flt

    This is definitely a confusing area, but I hope this helps.

    Ehren


    Monday, November 6, 2017 8:39 PM

All replies

  • Hi Ronen,

    The issue is caused by the fact that you're doing a merge (Table.NestedJoin) in the same query as a data source access (GetParam("Date"), which accesses the current workbook).

    You can resolve the issue by extracting the data source access into a separate query.

    DateParam
    = GetParam("Date")

    Then reference this new query from Result1. (However, you'll still want to use the function directly in your staging queries, since those are accessing data sources.)

    Result1

    let
    Source = Table.Buffer(Table.NestedJoin(StagingQry1,{"ProductID"},StagingQry2,{"ProductID"},"Qry",JoinKind.LeftOuter)),
    Flt = Table.SelectRows(Source, each [SellStartDate]=DateParam)
    in
    Flt

    This is definitely a confusing area, but I hope this helps.

    Ehren


    Monday, November 6, 2017 8:39 PM
  • Hi Ehren,

    thanks for that!!
    i didn't think of the function as another Staging query, but it solves the issue than thanks
    actually thinking about it again, the function is not necessary for this architecture, the only thing to make sure is that every data source - even tables within the excel sheet needs to be separated into a staging query.


    Regards,

    Ronen

     
    Tuesday, November 7, 2017 8:08 AM