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"