Forum Discussion

JørgenWulff's avatar
JørgenWulff
Frequent Visitor
3 years ago

Merging a query based on a restApI with a query based on an Excel table

I get an error, when I try to merge 2 tables in Power Query. 

The first table (Accounts) is created using Tokens in a RestAPI, while the second table is based on an Excel table (Accounts Hierarchy). Neither of the tables includes confidential information, but still get the error shown below:

 

Formula.Firewall: Query 'Query1' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

I found the article below from Microsoft:
https://learn.microsoft.com/en-us/power-query/data-privacy-firewall
But I am not able to create a new query that handles the partition that is described in the article.

The 2 Queries is as follows:

let
Source = Excel.CurrentWorkbook(){[Name="Kontohieraki"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Konto", Int64.Type}, {"Hovedområde", type text}, {"Ansvarlig", type text}})
in
#"Changed Type"

let
Source = GetData("accounts"),
#"Expanded {0}" = Table.ExpandRecordColumn(Source, "collection", {"accountNumber", "accountType", "balance", "blockDirectEntries", "debitCredit", "name", "accountingYears", "self", "vatAccount", "totalFromAccount", "contraAccount", "openingAccount", "accountsSummed"}, {"accountNumber", "accountType", "balance", "blockDirectEntries", "debitCredit", "name", "accountingYears", "self", "vatAccount", "totalFromAccount", "contraAccount", "openingAccount", "accountsSummed"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"debitCredit", "accountingYears", "self", "vatAccount", "blockDirectEntries", "contraAccount", "openingAccount", "accountsSummed"}),
#"Udvidet totalFromAccount" = Table.ExpandRecordColumn(#"Removed Columns", "totalFromAccount", {"accountNumber"}, {"accountNumber.1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Udvidet totalFromAccount",{{"name", type text}, {"accountNumber", Int64.Type}, {"balance", type number}, {"accountType", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"accountNumber", "KontoNr"}, {"name", "Kontonavn"}, {"accountNumber.1", "SumFra"}}),
#"Indsat sammenflettet kolonne" = Table.AddColumn(#"Renamed Columns", "Kontonr - navn", each Text.Combine({Text.From([KontoNr], "da-DK"), [Kontonavn]}, " - "), type text),
#"Flettede forespørgsler" = Table.NestedJoin(#"Indsat sammenflettet kolonne", {"KontoNr"}, Dim_AccountHierarchy, {"Konto"}, "Dim_AccountHierarchy", JoinKind.LeftOuter)
in
#"Flettede forespørgsler"

 

Any help will be much appreciated.

 

regards
Jørgen

2 Replies

  • ppm1's avatar
    ppm1
    Solution Sage

    Have you tried just ignoring privacy in that file in the settings?

     

    Pat

    • JørgenWulff's avatar
      JørgenWulff
      Frequent Visitor

      Hi Pat, 
      The problem is that it is a clients model and I want to keep security as high as possible. So if there is any other solution, I would prefer that.

      regards
      Jørgen