Power Query: FomulaFirewall with Invoked Custom Function

[ingeborg]

I have imported a CSV-file into Power BI and in Power Query I get one column from this file's table and try to run an API-function on the new table, resulting in this error:

Formula.Firewall: Query 'orgnr' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Import csv into table CSVfil

I import the CSV with this code (*** = private info):

let
    Source = Csv.Document(Web.Contents("https://***.sharepoint.com/***/Datatabeller/Enhetstabellene/Alle-orgnr/" & "20-01-23_enheter_alle.csv"),[Delimiter=";", Encoding=65001]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Forretningsadresse.landkode] = "NO")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Forretningsadresse.kommunenummer], "3"))
in
    #"Filtered Rows1"

Then I make a new query, and run a function on that query. This throws an error.

Start a new table with on of the columns

The table CSVfil have many columns I don't need. So I extract only one of the columns into a new query/table called orgnr.

let
    Source = Table.SelectColumns(CSVfil,{"Organisasjonsnummer"})
in
    Source

When invoking the function, I get the error.

I read this blogpost and tries to do the extra stage/step

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

let
    Source = orgnr,
    #"Invoked Custom Function" = Table.AddColumn(Source, "funkFirma2", each funkFirma2([Organisasjonsnummer]))
in
    #"Invoked Custom Function"

And then I tried to run the function. But then I get the error.

Turning the Privacy settings to ignore solves the error. But that is not a good solution. It was suggested in this video:

https://www.youtube.com/watch?v=l4b8yRUpaoM

I have also tries changing the settings of each permission in File > Options and settings > Data source settings with no luck.

The function

The function get information from an API with this code:

let funkFirma2 = (orgnr as text) =>
    let
        Kilde = Xml.Tables(Web.Contents("https://www.vismabizweb.no/soap/BizwebPartner.asmx/hentFirma2?brukernavn=***&passord=***&orgnr=" & orgnr & "&med_bransjer=true&med_kontakt_personer=true"))

    in
        Kilde
    
in 
    funkFirma2

How can I get rid of the error and make the function work, without risking terrible stuff with the privacy setting too low?

[ingeborg]

I have imported a CSV-file into Power BI and in Power Query I get one column from this file's table and try to run an API-function on the new table, resulting in this error:

Formula.Firewall: Query 'orgnr' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Import csv into table CSVfil

I import the CSV with this code (*** = private info):

let
    Source = Csv.Document(Web.Contents("https://***.sharepoint.com/***/Datatabeller/Enhetstabellene/Alle-orgnr/" & "20-01-23_enheter_alle.csv"),[Delimiter=";", Encoding=65001]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Forretningsadresse.landkode] = "NO")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Forretningsadresse.kommunenummer], "3"))
in
    #"Filtered Rows1"

Then I make a new query, and run a function on that query. This throws an error.

Start a new table with on of the columns

The table CSVfil have many columns I don't need. So I extract only one of the columns into a new query/table called orgnr.

let
    Source = Table.SelectColumns(CSVfil,{"Organisasjonsnummer"})
in
    Source

When invoking the function, I get the error.

I read this blogpost and tries to do the extra stage/step

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

let
    Source = orgnr,
    #"Invoked Custom Function" = Table.AddColumn(Source, "funkFirma2", each funkFirma2([Organisasjonsnummer]))
in
    #"Invoked Custom Function"

And then I tried to run the function. But then I get the error.

Turning the Privacy settings to ignore solves the error. But that is not a good solution. It was suggested in this video:

https://www.youtube.com/watch?v=l4b8yRUpaoM

I have also tries changing the settings of each permission in File > Options and settings > Data source settings with no luck.

The function

The function get information from an API with this code:

let funkFirma2 = (orgnr as text) =>
    let
        Kilde = Xml.Tables(Web.Contents("https://www.vismabizweb.no/soap/BizwebPartner.asmx/hentFirma2?brukernavn=***&passord=***&orgnr=" & orgnr & "&med_bransjer=true&med_kontakt_personer=true"))

    in
        Kilde
    
in 
    funkFirma2

How can I get rid of the error and make the function work, without risking terrible stuff with the privacy setting too low?

[v-lid-msft]

Hi @ingeborg ,

Could you please try to use the Table.Buffer function?

let
    Source = Table.SelectColumns(CSVfil,{"Organisasjonsnummer"}),
    Keep = Table.Buffer(Source)
in
    Keep

Best regards,

[Jimmy801]

Hello @ingeborg 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[ImkeF]

Hi @ingeborg  

sometimes it helps to convert the staging query into a function (without any parameters) instead.

So this would be:

() =>
let
    Source = Csv.Document(Web.Contents("https://***.sharepoint.com/***/Datatabeller/Enhetstabellene/Alle-orgnr/" & "20-01-23_enheter_alle.csv"),[Delimiter=";", Encoding=65001]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Forretningsadresse.landkode] = "NO")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Forretningsadresse.kommunenummer], "3"))
in
    #"Filtered Rows1"

and then you can call it like so in the subsequent query like so:

let
    Source = Table.SelectColumns(CSVfil(),{"Organisasjonsnummer"})
in
    Source

  or maybe its even this query that has to be turned into a function, just try it out.

[Jimmy801]

Hello @ingeborg 

you need to have the csv-query as well as the query, the invokes the data-source-function in one query

so this should work out for you

let
    Source = Csv.Document(Web.Contents("https://***.sharepoint.com/***/Datatabeller/Enhetstabellene/Alle-orgnr/" & "20-01-23_enheter_alle.csv"),[Delimiter=";", Encoding=65001]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Forretningsadresse.landkode] = "NO")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Forretningsadresse.kommunenummer], "3")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows1", "funkFirma2", each funkFirma2([Organisasjonsnummer]))
in
    #"Invoked Custom Function"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ingeborg]

Thank you!

But I need to run several different functions (API) on the same numbers (Organisasjonsnummer), so I need to be able to do this in different queries.

I know I may right-click and make a new query from a column, but then the it's connected to the source-csv directly and I need to connect it to the filtered table.

[Jimmy801]

Hello @ingeborg 

how about using this central query to apply all APIs needed?

Did you try to disable the firewall-function? (File -> Options & settings -> Options -> privacy -> Ignore privacy level)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ingeborg]

Yes, but I'm afraid of the consequences,

Turning the Privacy settings to ignore solves the error. But that is not a good solution. It was suggested in this video:

https://www.youtube.com/watch?v=l4b8yRUpaoM

I have also tries changing the settings of each permission in File > Options and settings > Data source settings with no luck.

[Jimmy801]

Hello @ingeborg 

so the only solution is to put everything in one query

Jimmy