Power Query: FomulaFirewall with Invoked Custom Function

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?

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

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