Forum Discussion

Lucian's avatar
Lucian
Icon for Responsive Resident rankResponsive Resident
6 years ago
Solved

Formula.Firewall Error in combination with PowerQuery function

Hello,   I am trying to create a "dynamic union" of identical tables from a Navision database.  Basicaly I need come "consolidated data" without using the Consolidated company from NAV. These tabl...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    try to "functionize" the Company-query like so: (adding " () => " at the start)

     

    () =>
let
    Source = Sql.Databases(NAVDataURL),
    DBSource = Source{[Name=NAVDBName]}[Data],
    dbo_Company = DBSource{[Schema="dbo",Item="Company"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_Company,{"timestamp", "Evaluation Company"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Name] = "Comp1" or [Name] = "Comp2"))
in
    #"Filtered Rows"

     

    and then call it like so: Company() 
    This is a function without a parameter, but you have to include the parenthesis.

     

    let
        Source = Company(),
        #"Invoked Custom Function" = Table.AddColumn(Source, "GetProjectsTable", each GetProjectsTable([Name]))
    in
        #"Invoked Custom Function"

     

Lucian's avatar
Lucian
Icon for Responsive Resident rankResponsive Resident
6 years ago

Hi Greg_Deckler ,

 

I would like to thank you for the replies on other posts... but for this one I have a problem reading it... 😁

Could you please post it again?

 

Kind Regards,

Lucian

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Oh, sorry, that is probably because that post was really, really short. I was just "invoking ImkeF ". She is one of, if not the best, Power Query person out there so when I find a Power Query question that I can't answer, I call her attention to it by mentioning her.

  • Lucian's avatar
    Lucian
    Icon for Responsive Resident rankResponsive Resident
    6 years ago

    Oh, thank you Greg_Deckler  for clarifications, and until ImkeF would have a chance to respond, I would do a quick update:

     

    The trick with "Ignoring the Privacy Levels" for the current file from here:  Data Privacy and the Formula Firewall in Power BI / Power Query - will help me avoid the error on the desktop but publishing the report will bring it back on the PBI service.

     

    Also the "staging" queries from here: Power Query Errors: Please Rebuild This Data Combination did not work either, or did not know how use it correctly.

     

    Any other ideeas?

     

    Kind Regards,

    Lucian

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      try to "functionize" the Company-query like so: (adding " () => " at the start)

       

      () =>
let
    Source = Sql.Databases(NAVDataURL),
    DBSource = Source{[Name=NAVDBName]}[Data],
    dbo_Company = DBSource{[Schema="dbo",Item="Company"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_Company,{"timestamp", "Evaluation Company"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Name] = "Comp1" or [Name] = "Comp2"))
in
    #"Filtered Rows"

       

      and then call it like so: Company() 
      This is a function without a parameter, but you have to include the parenthesis.

       

      let
          Source = Company(),
          #"Invoked Custom Function" = Table.AddColumn(Source, "GetProjectsTable", each GetProjectsTable([Name]))
      in
          #"Invoked Custom Function"

       

      • Lucian's avatar
        Lucian
        Icon for Responsive Resident rankResponsive Resident
        6 years ago

        Hello ImkeF ,

         

        Thank you veeeery much for your time and especially for this "quick fix". 😀 

        And many thanks to Greg_Deckler for bringing my problem to your attention, so I've solved my problem very quickly.

        I could only found a solution, by myself, by creating a manual list of values: {"Comp1", "Comp2"} that was used into the functions that get the table data in the "Filtered Rows" so I do not have to edit again every query.

        Still your solution is better, because I could use the "original" query with just a single line! Wow! And it worked! 😁

         

        One more question related to this problem: Is there any chance to benefit from the "Incremental Refresh" recently launched to Power BI Pro users? In this case where should I use the specific parameters RangeStart/RangeEnd - inside the query/function that generate the individual tables or in the final "consolidated" table?

         

        Kind Regards,

        Lucian