In the first two parts of this series (see here and here) I showed how Power BI/Power Query/Excel Get & Transform’s data privacy settings can influence whether query folding takes place or even whether a query is able to run or not. In this post I’m going to talk about the situations where, whatever data privacy level you use, the query will not run at all and you get the infamous Formula.Firewall error.
I’ll admit I don’t understand this particular topic perfectly (I’m not sure anyone outside the Power Query dev team does) so what I will do is explain what I do know, demonstrate a few scenarios where the error occurs and show how to work around it.
Assume you have the two data sources described in my previous posts: an Excel workbook that contains just a single day name, and the DimDate table in SQL Server that can be filtered by the day name from Excel. Let’s also assume that both data sources have their data privacy levels set to Public. The following query, called FilterDay, loads the data from Excel and returns a text value containing the day name:
let
Source =
Excel.Workbook(
File.Contents("C:\FilterParameter.xlsx"),
null, true),
FilterDay_Table =
Source{[Item="FilterDay",Kind="Table"]}[Data],
ChangedType =
Table.TransformColumnTypes(
FilterDay_Table,
{{"Parameter", type text}}
),
Output =
ChangedType{0}[#"Parameter"]
in
Output
Now, look at the following query:
let
Source =
Sql.Database(
"localhost",
"adventure works dw",
[Query="select DateKey, EnglishDayNameOfWeek
from DimDate"]),
FilteredRows =
Table.SelectRows(Source,
each ([EnglishDayNameOfWeek] = FilterDay)
)
in
FilteredRowsIt filters the contents of the DimDate table and only returns the rows where the EnglishDayNameOfWeek column matches the day name returned by the FilterDay query. Notice that there are two steps in the query, Source (which runs a SQL query) and FilteredRows (which does the filtering). Here’s the output:
As you can see from the screenshot, the query runs. In fact it runs whatever data privacy settings you have set on both the data sources, although it’s worth pointing out that if you use your own SQL in an M query (as I do in this case) this stops query folding in all subsequent steps, as described here.
Now take a look at the following version of the query:
let
Source =
Table.SelectRows(
Sql.Database(
"localhost",
"adventure works dw",
[Query="select DateKey,
EnglishDayNameOfWeek
from DimDate"]
),
each ([EnglishDayNameOfWeek] = FilterDay)
)
in
SourceThe important difference here is that there is now one step in this query instead of two: the query and the filtering take place in the same step. Even more importantly, regardless of the data privacy settings, the query fails with the error:
Formula.Firewall: Query ‘DimDate With Native Query Single Step Fails’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
The problem here is that the Power Query engine is not allowed to access two different data sources originating from different queries in the same step – as far as I understand it this is because it makes it too hard for the engine to work out whether a step connects to a data source or not, and so which data privacy rules should be applied.
At this point you might think that it’s straightforward to break your logic up into separate steps, as in the first example above. However there are some situations where it’s not so easy to work around the problem. For example, consider the following query:
let
Source =
Sql.Database(
"localhost",
"adventure works dw",
[Query="
select DateKey, EnglishDayNameOfWeek
from DimDate
where
EnglishDayNameOfWeek='" & FilterDay & "'"
]
)
in
SourceIn this example I’m dynamically generating the SQL query that is being run and passing the name of the day to filter by into the WHERE clause. In the two previous examples the query that was run had no WHERE clause and the filtering on day name took place inside Power BI – in this case the filtering is happening inside the query, so in order to generate the WHERE clause I have to refer to the value that the FilterDay query returns in the same step. Therefore, this query also gives the same Formula.Firewall error seen above.
How can you work around this? Well, the following version of the query that attempts to reference FilterDay in a separate step doesn’t work either:
let
DayAsStep = FilterDay,
Source =
Sql.Database(
"localhost",
"adventure works dw",
[Query="
select DateKey, EnglishDayNameOfWeek
from DimDate
where
EnglishDayNameOfWeek='" & DayAsStep & "'"
]
)
in
SourceLuckily, it turns out that if you use the Value.NativeQuery() function to run your query instead you can avoid the error. As I showed here, you can use this function to pass parameters to SQL queries. If you generate the record containing the parameters for the query as a separate step (called ParamRecord here), like so:
let
Source = Sql.Database("localhost", "adventure works dw"),
ParamRecord = [FilterParameter=FilterDay],
Query = Value.NativeQuery(
Source,
"select DateKey, EnglishDayNameOfWeek
from DimDate
where
EnglishDayNameOfWeek=@FilterParameter",
ParamRecord)
in
QueryThen the query runs successfully.
There is another way to avoid the error. In all the examples above I have two queries: one to get data from Excel, one to get filtered data from SQL Server. If these two queries are combined into a single query, it doesn’t matter if data from different data sources is accessed in the same step. So, for example, unlike all of the queries above the following query does not reference any other queries; instead it gets the day name from the Excel workbook in the ExcelSource step and then runs the dynamic SQL query in the SQLSource step, and runs successfully:
let
ExcelSource =
Excel.Workbook(
File.Contents("C:\FilterParameter.xlsx")
, null, true),
FilterDay_Table =
ExcelSource{[Item="FilterDay",Kind="Table"]}[Data],
ChangedType =
Table.TransformColumnTypes(FilterDay_Table,
{{"Parameter", type text}}),
FilterDayStep =
ChangedType{0}[#"Parameter"],
SQLSource = Sql.Database(
"localhost",
"adventure works dw",
[Query="
select DateKey, EnglishDayNameOfWeek
from DimDate
where
EnglishDayNameOfWeek='"
& FilterDayStep &
"'" ])
in
SQLSourceClearly the M engine doesn’t get confused about accessing data from different sources in the same step if those data sources are created in the same query.
Of course you can avoid the Formula.Firewall error and make query folding happen as often as possible by turning off data privacy checks completely in the Options dialog. This will be the subject of the next post in this series.
Thanks for the tip, Chris! Would never have thought to write the last bit of M code, where you use both data sources in the same script. I always thought that was the underlying reason for the error. Brilliant workaround!
Thanks Chris, this is good stuff and it works well in PBI Desktop.
But I cannot get it to work in the service or in Azure AS. Did manage to get it working there already?
What kind of problem are you getting? Have you installed the latest version of the Gateway (if you’re using the Gateway?). I’m not sure what differences there are between the versions of the M engine Power BI and Azure AS, unfortunately, so it could be that there are some things that work with Power BI and don’t with Azure AS.
Thanks! I’m getting formula firewall warning in AS and ” You can’t schedule refresh for this dataset because one or more sources currently don’t support refresh. ” in the service. Using newest enterprise gateways for local MS SQL and local files (xlsx). As long as I don’t combine the sources, refreshes work fine, but once combined they fail.
I have to ask: have you turned off data privacy checks in Power BI Desktop?
Hi Chris
I’ve trying to find a way to eliminate the firewall error. The only way I found is to disable the global privacy setting within Power Query. I’m not using any sql server in my power query queries, only accessing to xls files located in a network folder.
This Power Query file contains dashboards that are used by different users and it obliges me to make physically a change on each file distributed (on each pc). Is there a way to simply distribute the file without having to change the gloval privacy settings ?
thanks
patrice
No, I don’t think there is (and that’s deliberate). There should be a way of avoiding the firewall error without disabling privacy settings if you are only working with Excel files though…
I have a hypothesis of what happens. I do not know if you will agree or not.. i believe that the root cause of this issue is the “lazy evaluation” in M.
This error occurs when you try to open a file that is referenced indirectly, por example if you use a function to open a file inside a query.
When the “fast combine” security option is not enabled PowerQuery tries to verify the security of each file inmediatelly but it can do it because it is deferred because the lazy evaluation… When you enabled fast combine then it works inmediatelly or if you use a workaround with intermediate files. Intermediate files cause the “evaluation of the file” and load it in another place…
Hi Chris,
Looking for your help on my case.
In my report i am using OLEDB connection instead of SQL. Me too pasing same parameters.
How can i solve this Firewall issue here.
Have you tried using Value.NativeQuery as shown in the blog post? I think it might work with an OLEDB connection.
Hi Chris,
Here is my thread
https://community.powerbi.com/t5/Desktop/Formula-Firewall-step-Invoked-Custom-Function-references-other/td-p/480465
Hi Chris,
i Have created function with below query
(f_tag as text,d_tag as text,v_tag as text,start as text,end as text)=>
let
Source = OleDb.DataSource(“Provider=PIOLEDB.1;Data Source=XXX.com;Time Zone=Server;”),
PQ=Value.NativeQuery(Source,
“select p1.time,p1.tag as Flow_PI_Tag, p1.value as Flow_Value,p2.tag as Vol_PI_Tag, p2.value as Vol_Value,p3.tag as Dens_PI_Tag, p3.value as Dens_Value
from piinterp2 p1
INNER JOIN piinterp2 p2 ON p1.time = p2.time
INNER JOIN piinterp2 p3 ON p1.time = p3.time
WHERE
p1.timestep = ’00:01:00′
and p1.tag = @ftag
and p1.time between @sdate and @edate
and p2.tag = @vtag
and p3.tag = @dtag”,
[sdate=start,edate=end,ftag=f_tag,dtag=d_tag,vtag=v_tag])
in
PQ
Correct me if i missed anything.
While i passing the parameter am getting syntax error, but same i can able to execute in PI server.
Error :
DataSource.Error: OLE DB: [SQL Parser] [Line 7] Syntax error near ‘@’.
Details:
DataSourceKind=OleDb
DataSourcePath=data source=XXXX.com;provider=PIOLEDB.1;time zone=Server
Message=[SQL Parser] [Line 7] Syntax error near ‘@’.
ErrorCode=-2147217900
I don’t know what’s going on here, I’m afraid, but it looks like it is a problem with the SQL and/or the OLE DB Provider and not the M code.
Very useful blog post – helped me solve an issue with the dreaded formula firewall, thanks Chris. I liked the look of Value.NativeQuery(), but unfortunately it doesn’t seem to work with Odbc sources. (It would have been nice if that was documented by Microsoft as it would have saved me time in a fruitless refactoring exercise). Nonetheless, your second tip of combining into one query did work.
Hi Chris,
Really great posts! – make formula.firewall error more understandable.
I am not sure however if we can apply all of it to webservice queries – can we treat this type of service equally to a database query.
I have one query that returns a table with text parameters to be send to other query – where the text parameter is to be send to a Web API call.
I have created a function that calls the page and converts it to Json.
In the first query I have opened the Excel file and read all the rows – seems to be trivial, doesn’t it ?
In the second query, I have started with referencing the first one (instead of doing direct access to the source).
So I have something like
let
referencedQuery = Query1,
webParameter = Source{0}[#”AddressParameter”],
importedJson = fx_getWebContent( WebParameter )
in
importedJson
The funny things is that as soon as I call the API function in Query1 – it works.
When I am trying to follow the “staging” pattern – it does not work.
What I am missing here ?
I have not found this type of example where web call was merged with other data like this with unchecked “Ignore privacy levels…” I would really appreciate your comment for this scenario.
It’s difficult to say – there are all kinds of undocumented quirks with this functionality and no-one outside the dev team really knows how it works.
Thanks, Chris. I knew a google search would head me here. I got a loaner laptop at work and ended up with a Formula.Firewall. I went to the Query Options > Privacy > and selected Always ignore Privacy Level settings which removed the error.
It’s not really an error, it’s a safety warning. The next post in this series explains why: https://blog.crossjoin.co.uk/2017/07/04/data-privacy-settings-in-power-bipower-query-part-4-disabling-data-privacy-checks/
Hi Chris, this is a really useful Blog, thanks. It solved my problem of the Firewall blocking other referenced queries, I fixed that by bringing them into the same query.
Do you have any insight into how the new Dataflows feature treats Privacy levels? I’m trying to combine some data from a dataflow with the google knowledge graph API.
I have a function which iterates through the query and returns the company website for a given company name. This works fine in the Desktop (with Privacy checks enabled and all Privacy levels set to the same level) but then fails in the service with the error
[Unable to combine data] Section1/CSD Company Groups/Filtered Rows1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.. The exception was raised by the IDbCommand interface. Table: CSD Companies.
It doesn’t seem to matter how the privacy levels are set in the file the error persists in the service. I wonder if dataflows are stuck at Private privacy level in the service and can’t be combined with other sources?
I don’t know how dataflows handle privacy, but are you actually hitting this problem instead? https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/
Thanks for the response Chris, I am using the Web.contents query option already but the problem seems related to how privacy levels are set in the service for dataflows. Frustrating that there is no clear documentation on this from Microsoft
Hmm, I’ve been doing some more research into this and I *might* have an answer for you. Blog post coming soon…
Sounds great, thanks Chris!
Hi Chris
I am struggling with refreshing data coming off multiple web API’s which I combine using a parameter in the URL. The data refreshes fine in Power BI desktop, but I cannot get it to refresh within the service.
I’ve looked at your blog post and the below link as well as ignoring the Privacy Settings in the desktop, but I still cannot figure out why I get an error message and how to split the queries into seperate staging queries in Power BI desktop:
https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
My error message is below: If I try and create seperate staging tables I get error messages. Any assistance on this would be greatly appreciated!
Error Message:
[Unable to combine data] Section1/V7ClientsStaging2/Added Custom1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: V7Clients.
Original Query (which gives an error on Added Custom 1 step) when uploaded to service:
let
Source = {
ClientDetails[AreaGuid]
},
#”ListOfLists” = List.Combine(Source),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “AreaGuidsForFunction”}}),
#”Expanded AreaGuidsForFunction” = Table.ExpandListColumn(#”Renamed Columns”, “AreaGuidsForFunction”),
#”Added Custom1″ = Table.AddColumn(#”Expanded AreaGuidsForFunction”, “Custom”, each AssessmentsInfo([AreaGuidsForFunction])),
#”Removed Errors” = Table.RemoveRowsWithErrors(#”Added Custom1″, {“Custom”}),
#”Merged Queries” = Table.NestedJoin(#”Removed Errors”,{“AreaGuidsForFunction”},ClientDetails,{“RootAreaGuid”},”ClientDetails”,JoinKind.LeftOuter),
#”Removed Columns” = Table.RemoveColumns(#”Merged Queries”,{“Custom”}),
#”Expanded ClientDetails” = Table.ExpandTableColumn(#”Removed Columns”, “ClientDetails”, {“ClientName”}, {“ClientName”})
in
#”Expanded ClientDetails”
And this is how I’ve attempted to split the query out, where I still get the same error message on the second staging table:
Staging Table 1:
let
Source = {
ClientDetails[RootAreaGuid]
},
#”ListOfLists” = List.Combine(Source),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “AreaGuidsForFunction”}}),
#”Expanded AreaGuidsForFunction” = Table.ExpandListColumn(#”Renamed Columns”, “AreaGuidsForFunction”)
in
#”Expanded AreaGuidsForFunction”
Staging Table 2
let
Source = V7ClientsStaging1,
#”Added Custom1″ = Table.AddColumn(Source, “Custom”, each AssessmentsInfo([AreaGuidsForFunction])),
#”Removed Errors” = Table.RemoveRowsWithErrors(#”Added Custom1″, {“Custom”})
in
#”Removed Errors”
Final table (Table 3):
let
Source = V7ClientsStaging2,
#”Merged Queries” = Table.NestedJoin(Source,{“AreaGuidsForFunction”},ClientDetails,{“RootAreaGuid”},”ClientDetails”,JoinKind.LeftOuter),
#”Expanded ClientDetails” = Table.ExpandTableColumn(#”Merged Queries”, “ClientDetails”, {“ClientName”}, {“ClientName”}),
#”Removed Columns” = Table.RemoveColumns(#”Expanded ClientDetails”,{“Custom”})
in
#”Removed Columns”
Any insights on being able to fix this would be most welcome, as I’m really struggling to get this to work.
It looks like you have a function defined called AssessmentsInfo – can you try adding the definition of that function as a step inside your original query?
Hi Chris
Thanks for the assistance on this, I’m still fresh to the use of parameters and functions. I’ve tried to follow putting the definition of the AssessmentsInfo function inside the original query as per your advice above, but it’s giving me a column of functions without putting in the parameters. Would you be able to identify where I’m going wrong on this?
ORIGINAL QUERY:
let
Source = {
ClientDetails[RootAreaGuid]
},
#”ListOfLists” = List.Combine(Source),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “AreaGuidsForFunction”}}),
#”Expanded AreaGuidsForFunction” = Table.ExpandListColumn(#”Renamed Columns”, “AreaGuidsForFunction”),
#”Added Custom1″ = Table.AddColumn(#”Expanded AreaGuidsForFunction”, “Custom”, each AssessmentsInfo([AreaGuidsForFunction])),
#”Removed Errors” = Table.RemoveRowsWithErrors(#”Added Custom1″, {“Custom”}),
#”Merged Queries” = Table.NestedJoin(#”Removed Errors”,{“AreaGuidsForFunction”},ClientDetails,{“RootAreaGuid”},”ClientDetails”,JoinKind.LeftOuter),
#”Removed Columns” = Table.RemoveColumns(#”Merged Queries”,{“Custom”}),
#”Expanded ClientDetails” = Table.ExpandTableColumn(#”Removed Columns”, “ClientDetails”, {“ClientName”}, {“ClientName”})
in
#”Expanded ClientDetails”
ORIGINAL FUNCTION:
(AreaAPI as text) as list =>
let
Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
[
RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”
]
))
in
Source
FUNCTION INSIDE ORIGINAL QUERY (that’s not working):
let
Source = {
ClientDetails[RootAreaGuid]
},
#”ListOfLists” = List.Combine(Source),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “AreaGuidsForFunction”}}),
#”Expanded AreaGuidsForFunction” = Table.ExpandListColumn(#”Renamed Columns”, “AreaGuidsForFunction”),
#”Added Custom1″ = Table.AddColumn(#”Expanded AreaGuidsForFunction”, “Custom”, each (
(AreaGuidsForFunction as text) as list =>
let
Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
[
RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”
]
))
in
Source
)),
#”Merged Queries” = Table.NestedJoin(#”Added Custom1″,{“AreaGuidsForFunction”},ClientDetails,{“RootAreaGuid”},”ClientDetails”,JoinKind.LeftOuter),
#”Expanded ClientDetails” = Table.ExpandTableColumn(#”Merged Queries”, “ClientDetails”, {“ClientName”}, {“ClientName”})
in
#”Expanded ClientDetails”
Thanks again.
Hi Chris
I got some feedback from Imke Feldmann via the Power BI community how to include the function definition within the main query.
https://community.powerbi.com/t5/Desktop/Define-and-invoke-function-within-query/td-p/619982
Re-writing the query to include the function definition works correctly. But I’m still getting the same recurring issue when using parameters in URL’s, irrespective of whether I change privacy settings, create staging queries, try to rewrite queries:
[Unable to combine data] Section1/V7ClientsStaging2/Added Custom1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: V7Clients
Any additional suggestions I could look at to sort this error out would be most appreciated, as I cannot figure it out.
Instead of using the AreaAPI parameter direct inside the code that generates your URL, something I have seen work is to create an extra step that takes the value of the parameter, then use that step inside the code to generate the URL instead. For example:
(x) =>
let
y = x,
CallWeb = Web.Contents(“http://abc.com”, [RelativePath=”/xyz/” & y])
in
CallWeb
Hi,
I’ve got the same issue with my query.
Thing is that within my query, I first have to merge from different sources to come to my final sheet, I have a forum thread here: https://community.powerbi.com/t5/Power-Query/Formula-Firewall-error-calling-a-web-api-get-s-error-when-used/m-p/714077
My query then looks like this, please note that within the geocoder api, I’ve changed the app id and app code to xx due to privacy reasons.
let
geocode = (herecoding as text) =>
let
coding = herecoding,
PostContent=””,
Bron = Json.Document(Web.Contents(“https://geocoder.api.here.com/6.2/geocode.json?app_id=xxxx&app_code=xxxx&searchtext=”&coding&””, [Headers=[#”Accept-Encoding”=”gzip”]]), Compression.GZip),
#”Geconverteerd naar tabel” = Record.ToTable(Bron),
#”Value uitgevouwen” = Table.ExpandRecordColumn(#”Geconverteerd naar tabel”, “Value”, {“View”}, {“Value.View”}),
#”Value.View uitgevouwen” = Table.ExpandListColumn(#”Value uitgevouwen”, “Value.View”),
#”Value.View uitgevouwen1″ = Table.ExpandRecordColumn(#”Value.View uitgevouwen”, “Value.View”, {“Result”}, {“Value.View.Result”}),
#”Value.View.Result uitgevouwen” = Table.ExpandListColumn(#”Value.View uitgevouwen1″, “Value.View.Result”),
#”Value.View.Result uitgevouwen1″ = Table.ExpandRecordColumn(#”Value.View.Result uitgevouwen”, “Value.View.Result”, {“Location”}, {“Value.View.Result.Location”}),
#”Value.View.Result.Location uitgevouwen” = Table.ExpandRecordColumn(#”Value.View.Result uitgevouwen1″, “Value.View.Result.Location”, {“DisplayPosition”}, {“Value.View.Result.Location.DisplayPosition”}),
#”Value.View.Result.Location.DisplayPosition uitgevouwen” = Table.ExpandRecordColumn(#”Value.View.Result.Location uitgevouwen”, “Value.View.Result.Location.DisplayPosition”, {“Latitude”, “Longitude”}, {“Value.View.Result.Location.DisplayPosition.Latitude”, “Value.View.Result.Location.DisplayPosition.Longitude”})
in
#”Value.View.Result.Location.DisplayPosition uitgevouwen” ,
Source = Excel.Workbook(Web.Contents(“https://vanbruggen.sharepoint.com/sites/ManagementInformatie/Gedeelde%20%20documenten/Excel 2.0/Afspraken 2018 2.0 (nieuw).xlsx”), null, true),
Blad1_Sheet = Source{[Item=”Blad1″,Kind=”Sheet”]}[Data],
#”Headers met verhoogd niveau” = Table.PromoteHeaders(Blad1_Sheet, [PromoteAllScalars=true]),
#”Query toegevoegd” = Table.Combine({#”Headers met verhoogd niveau”, Afspraken2017, #”Blad1 (2)”}),
#”Rijen gefilterd” = Table.SelectRows(#”Query toegevoegd”, each ([Jaar] 2019)),
#”Query toegevoegd1″ = Table.Combine({#”Rijen gefilterd”, #”Rapportage Inbound 0800″}),
#”Kolommen verwijderd” = Table.RemoveColumns(#”Query toegevoegd1″,{“Column50”, “Column51”, “Column52”, “Column53″}),
#”Type gewijzigd” = Table.TransformColumnTypes(#”Kolommen verwijderd”,{{“Beldatum”, type date}}),
#”Aangepaste kolom toegevoegd” = Table.AddColumn(#”Type gewijzigd”, “DatumKey”, each Date.ToText([Beldatum],”yyyyMMdd”)),
#”Filtered Rows” = Table.SelectRows(#”Aangepaste kolom toegevoegd”, each true),
#”Renamed Columns” = Table.RenameColumns(#”Filtered Rows”,{{“CTPID”, “Afspraken”}}),
#”Type gewijzigd1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“Huisnummer”, type text}, {“Postcode”, type text}}),
#”Replaced Value1″ = Table.ReplaceValue(#”Type gewijzigd1″,null,””,Replacer.ReplaceValue,{“Huisnummer”, “Postcode”}),
#”Renamed Columns1″ = Table.RenameColumns(#”Replaced Value1″,{{“Toestemming Mailing?”, “Toestemming Mailing.2″}}),
#”Aangepaste kolom toegevoegd1″ = Table.AddColumn(#”Renamed Columns1”, “Here geo coding”, each [Straatnaam]&” “&[Huisnummer]&” “&[Woonplaats]&” “&[Postcode]),
#”Split Column by Position” = Table.SplitColumn(#”Aangepaste kolom toegevoegd1″, “Here geo coding”, Splitter.SplitTextByPositions({0, 2}, true), {“Here geo coding.1”, “Here geo coding.2″}),
#”Added Custom” = Table.AddColumn(#”Split Column by Position”, “Here geocoding”, each [Straatnaam]&” “&[Huisnummer]&” “&[Woonplaats]),
#”Merged Queries” = Table.NestedJoin(#”Added Custom”, {“Here geocoding”}, #”Here geo coding”, {“searchText”}, “Here geo coding”, JoinKind.LeftOuter),
#”Expanded Here geo coding” = Table.ExpandTableColumn(#”Merged Queries”, “Here geo coding”, {“Batch 1 here.Lat”, “Batch 1 here.Long”}, {“Batch 1 here.Lat”, “Batch 1 here.Long”}),
#”Replaced Value” = Table.ReplaceValue(#”Expanded Here geo coding”,null,”niks”,Replacer.ReplaceValue,{“Here geocoding”}),
#”Invoked Custom Function” = Table.AddColumn(#”Replaced Value”, “coding”, each geocode([Here geocoding])),
#”Expanded coding” = Table.ExpandTableColumn(#”Invoked Custom Function”, “coding”, {“Value.View.Result.Location.DisplayPosition.Latitude”, “Value.View.Result.Location.DisplayPosition.Longitude”}, {“Value.View.Result.Location.DisplayPosition.Latitude”, “Value.View.Result.Location.DisplayPosition.Longitude”}),
#”Changed Type” = Table.TransformColumnTypes(#”Expanded coding”,{{“Value.View.Result.Location.DisplayPosition.Latitude”, type number}, {“Value.View.Result.Location.DisplayPosition.Longitude”, type number}})
in
#”Changed Type”
Do you have any idea how I can fix this?
It’s hard to say, but the first thing to try would be to use the Query and RelativePath options for Web.Contents: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/
Hi Chriss, I couldn’t reply to your reply so I do it this way.
When I rewrite the function with the Query and Relative Path option like this
.= (herecoding as text) =>
let
coding = herecoding,
Bron = Json.Document(Web.Contents(“https://geocoder.api.here.com/6.2″,[RelativePath=”geocode.json?app_id=2OepPcarcchXJByTkta3&app_code=Uus8drQxI0xcbkmxAjZ18A&”,Query=[searchtext=coding]]), Compression.GZip),
I get the error:
“Value cannot be null. Parameter name: str.
Does any one have any idea how I can fix this?
Hi Chris
Not sure if you will read this, but thank you so much for your work and blogs.
I have been going a bit nuts trying to get a query to DB2 to add an extra part to the SQL where clause based on a query from Oracle.
I started with a post from Philip Seamark using dynamic SQL, but that would not refresh in the service, for the reasons you go into.
https://radacad.com/dynamic-sql-using-power-query
I read where you edited 2 queries into one query and while I am not an M expert, I have stuck with it and managed to get it to work.
What I wasn’t sure was would this refresh in the service, but it does!
Strange how you can trick the engine.
My POC code is below.
Many thanks
Oliver
let
OracleSource = Oracle.Database(“//someoracleserver.somecompany.com:1111/abcd”,
[Query=”select distinct CALENDAR_ID_LY_COMP
from st_comp_dates_2
where date_ty – date_ly_comp 364
and date_ty = to_date(sysdate-100)
order by CALENDAR_ID_LY_COMP”, CreateNavigationProperties=false]),
#”Changed Type” = Table.TransformColumnTypes(OracleSource,{{“CALENDAR_ID_LY_COMP”, type text}}),
Custom1 = Table.InsertRows(#”Changed Type”, 0, { [CALENDAR_ID_LY_COMP = “null”] }),
Source2 = #”Changed Type”,
#”Changed Type2″ = Table.TransformColumnTypes(Source2,{{“CALENDAR_ID_LY_COMP”, type text}}),
myList = Table.ToList(#”Changed Type2″),
EDWSource = DB2.Database(“someDB2server.unix.somecompany.com:11111”, “EDWP”,
[Implementation=”Microsoft”, Query=”SELECT * FROM DM.DIM_CALENDAR WHERE CALENDAR_ID in (” & Text.Combine(myList, “,”) & “)”,
CreateNavigationProperties=false, CommandTimeout=#duration(0, 0, 30, 0)])
in
EDWSource
This actually worked for me. Thank you so much
Hi,
I am trying to call power BI Table using JSON query what would be the correct syntax, i tried,
let
Source = #”1_SourceTable”,
ToArray = Table.ToRecords(Source),
ToJsonBinary = Json.FromValue(ToArray),
in
LinesFromBinary
it there anything that i am missing here
Can you try
let
Source = #”1_SourceTable”,
ToArray = Table.ToRecords(Source),
ToJsonBinary = Json.FromValue(ToArray),
in
ToJsonBinary
Hi,
Maybe I’m don’t fully understand your blog now. Could you advise how can I eliminate the error?
let
Source = Table.Combine({DWG_ARD_01, DWG_ARD_02, DWG_ARD_03}),
#”Removed Other Columns” = Table.SelectColumns(Source,{“Doc”, “Class”}),
#”Removed Duplicates” = Table.Distinct(Source, {“Doc”}),
#”Filtered Rows” = Table.SelectRows(#”Removed Duplicates”, each Text.Contains([Doc], fParameter(“F1″)))
in
#”Filtered Rows”
///Function is coming from here.
(FLT as text) =>
let
Source = Excel.CurrentWorkbook(){[Name=”DOC_TYPE”]}[Content],
value = Source{[FLT = FLT]}[TYPE]
in
value
Tried the above code and the value gets double while publishing. Example: I have a column name sales and I entered 4 in excel and once it get refreshed successfully the power bi shows 8 in power bi service window.
Hi, Chris,
I have a headache! I have a query whose data source is SSAS, but the database name of the query is uncertain, so I need to set a parameter for the database name in PQ, and then save it as PBIT, so that the user can enter the specified database name, that’s all work around.
But the user does not want to manually enter the database name, so I created another query, obtained these database names from a TXT file (stored a list of all database names), and then passed the query as a parameter to the first query, after I try it I get error “Formula.Firewall… Please rebuild this data combination.”,
I have read your blog but this seems to be impossible to solve with Value.NativeQuery, and it is also impossible to merge the data sets of the two queries, because the first data source (SSAS) needs to rely on the database name provided by the second data source (TXT)
So, How can I fix this?
Appreciation！