Define and invoke function within query

[Anonymous]

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. The error message is: 

[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.

I have tried privacy settings, staging tables (as per https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/) and still cannot get it to work.

A recommendation off another blog was to add the function definition into the main query. I'm struggling with the syntax however to get this to work. Any assistance would be appreciated.

Main Query (the function is AssessmentsInfo, bolded below):

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])),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter),
#"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"})
in
#"Expanded ClientDetails"

AssessmentsInfo function definition:

(AreaAPI as text) as list =>

let
Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
[
RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”

]

))
in
Source

AssessmentsInfo function definition within the Main Query (not working, gives a column of non invoked functions):

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 

(AreaAPI 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"

Any assistance on the syntax of adding the function definition within the query would be much appreciated!

[Greg_Deckler]

@ImkeF might be able to assist.

[ImkeF · Accepted solution]

That would be done like so:

let

	AssessmentsInfoFunction = (AreaAPI as text) as list =>
	let
		Source = Json.Document(Web.Contents(“https://abc.xxx.com”,
		[
		RelativePath= “/api/v1/gateway/platform/”&AreaAPI&”/assessmentcriteria”
		]
		))
	in
		Source,

	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 AssessmentsInfoFunction([AreaGuidsForFunction])),
	#"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"AreaGuidsForFunction"},ClientDetails,{"RootAreaGuid"},"ClientDetails",JoinKind.LeftOuter),
	#"Expanded ClientDetails" = Table.ExpandTableColumn(#"Merged Queries", "ClientDetails", {"ClientName"}, {"ClientName"})
in
	#"Expanded ClientDetails"

[Anonymous]

@Greg_Deckler Thanks for the referral.

@ImkeF Thanks so much for showing me how to define and and invoke a function within a query. This worked correctly. 

I'm still however having the issue when uploading to Power BI service: [Unable to combine data] Section1/V7ClientsStaging1/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: V7ClientsStaging1.

This suggestion to add the definition of the main function came from Chris Webb's blog. 

https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-form...

At this point I'm at a complete loss on how to fix this error, I've tried privacy settings, staging tables in multiple forms, rewriting queries. Any other suggestions I could pursue would be most welcome. 

[ImkeF]

Please check out this article: 

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

Don't know if it contains sth useful for your case, though.

Have you tried integrating the content of your "Source"-step as well?

[Anonymous]

@ImkeF 

I had a look at the blog entry you suggested without any luck. Thanks for the input on this though. 

When you say "Have you tried integrating the content of your "Source"-step as well?" Are you referring to the Source as below? 

If so, I tried adding the query content into the step directly, with the source being a dataflows query. Still no change 😞

Source

[Anonymous]

Hi There,

I'm also struggling with the same error in powerbi.com. I'm trying to reverse geolocation information based on Lat and Long through my google maps API and source of the information(lat & long) is located on a sharepoint folder.

My PowerBi pbix file has the Privacy setting adjusted. 

This following error happens when I invoked a custom function with the same data source query,
"[Unable to combine data] Section1/Location/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination"

This following error happens when I first stage the source information and then creating a secoud query to referrence the stage source with the added customer function
[Unable to combine data] Section1/Query1 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

As you can see, stagging for not stagging the error persists in the cloud 

let
Source = SharePoint.Files("https://oursharepointfolder/", [ApiVersion = 15]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
Navigation1 = Source{0}[Content],
#"Imported Excel" = Excel.Workbook(Navigation1),
#"Expanded Data" = Table.ExpandTableColumn(#"Imported Excel", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sheet1", type text}, {"Site Name", type text}, {"Address", type text}, {"Co-Ordinates", type number}, {"Co-Ordinates_1", type number}, {"Supplier", type text}, {"Solution", type text}, {"Curcuit ID", type text}, {"Sheet1_2", type text}, {"Sheet", type text}, {"false", type logical}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each ([#"Co-Ordinates"] <> null)),
#"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "Location", each Text.Combine({Text.From([#"Co-Ordinates"], "en-US"), Text.From([#"Co-Ordinates_1"], "en-US")}, ","), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Location", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Query2 (2)", each #"Query2 (2)"([Location])),
#"Expanded Query2 (2)" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query2 (2)", {"street_address", "route", "postal_code", "localitypolitical", "administrative_area_level_1political", "countrypolitical"}, {"street_address", "route", "postal_code", "localitypolitical", "administrative_area_level_1political", "countrypolitical"})
in
#"Expanded Query2 (2)"

###########################################################################################
Query 2 is my custom Fuction query

let

Findaddress = (addresslocation) =>

let
Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),
results = Source[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"formatted_address", "types"}, {"formatted_address", "types"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1", {"types", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Values", each ([types] <> "administrative_area_level_2political" and [types] <> "administrative_area_level_3political" and [types] <> "politicalsublocalitysublocality_level_1")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"Reversed Rows" = Table.ReverseRows(#"Transposed Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true])

in
#"Promoted Headers"

in

Findaddress
########################################################################################

I'm not sure how else to structure the query so that it works in powerbi.com service. Hope there is a way?

Kind regards,

Rayno

[ImkeF]

The article I referenced in my previous post states that you have to use query parameters to make this successful:

so instead of this:

Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),

you have to write it like so:

Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json",
Query=[latlng=addresslocation, key=Keyhasbeeneditedout]])),

[Anonymous]

Hi I am not able to change Schema name via parameter. Could you please look in to my original post and help me out? 

https://community.powerbi.com/t5/Desktop/Schema-change-to-multiple-queries-SAP-hana-and-Direct-query...

Thanks

[Anonymous]

Hi I am not able to change schema name vi Parameter. Can you help me on this? please see my originla post here. Thanks.

[rafael1xkr]

I have same problem with a public api in Power BI Service
Could you help me?

let

    Moeda = (CodMoeda as text) =>
 
let
    Source = Json.Document(Web.Contents("https://olinda.bcb.gov.br",
    [
        RelativePath= "/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='"&CodMoeda&"'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json",
        Query=[select="cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletim"]])),
    #"Convertido para Tabela" = Record.ToTable(Source),
    #"Linhas Filtradas" = Table.SelectRows(#"Convertido para Tabela", each ([Name] = "value")),
    #"Value Expandido" = Table.ExpandListColumn(#"Linhas Filtradas", "Value")
in
    #"Value Expandido",

    Fonte = Json.Document(Web.Contents("https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/Moedas?$top=100&$format=json&$select=simbolo,nomeFormatado,tipoMoeda")),
    #"Convertido para Tabela" = Record.ToTable(Fonte),
    #"Linhas Filtradas" = Table.SelectRows(#"Convertido para Tabela", each ([Name] = "value")),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Linhas Filtradas",{"Value"}),
    #"Value Expandido" = Table.ExpandListColumn(#"Outras Colunas Removidas", "Value"),
    #"Value Expandido1" = Table.ExpandRecordColumn(#"Value Expandido", "Value", {"nomeFormatado", "simbolo"}, {"nomeFormatado", "simbolo"}),
    #"Função Personalizada Invocada" = Table.AddColumn(#"Value Expandido1", "Consulta1 (2)", each Moeda([simbolo])),
    #"Consulta1 (2) Expandido" = Table.ExpandTableColumn(#"Função Personalizada Invocada", "Consulta1 (2)", {"Value"}, {"Value"}),
    #"Value Expandido2" = Table.ExpandRecordColumn(#"Consulta1 (2) Expandido", "Value", {"cotacaoCompra", "cotacaoVenda", "dataHoraCotacao", "tipoBoletim"}, {"cotacaoCompra", "cotacaoVenda", "dataHoraCotacao", "tipoBoletim"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Value Expandido2",{{"nomeFormatado", type text}, {"simbolo", type text}, {"cotacaoCompra", type number}, {"cotacaoVenda", type number}, {"dataHoraCotacao", type datetime}, {"tipoBoletim", type text}}),
    #"Data Extraída" = Table.TransformColumns(#"Tipo Alterado",{{"dataHoraCotacao", DateTime.Date, type date}})
in
    #"Data Extraída"

---

Example API return USD

https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='USD'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json&$select=cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletim

@ImkeF wrote:

The article I referenced in my previous post states that you have to use query parameters to make this successful:

so instead of this:

Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json?latlng="&addresslocation&"&key=Keyhasbeeneditedout"])),

you have to write it like so:

Source = Json.Document(Web.Contents("https://maps.googleapis.com",
[RelativePath="maps/api/geocode/json",
Query=[latlng=addresslocation, key=Keyhasbeeneditedout]])),

---

[ImkeF]

You have to adjust the first parameters of your Web.Contents-functions with longer URLs that actually return something: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/.

And don't worry, that will be overwritten by the relative path and query parameters, once the query runs.

So sth like this for the first Web.Contents (don't know what shall go into the 2nd): 

let

    Moeda = (CodMoeda as text) =>
 
let
    Source = Json.Document(Web.Contents("https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='USD'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json&$select=cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletim",
    [
        RelativePath= "/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@mhttps://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/CotacaoMoedaPeriodo(moeda=@moeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='USD'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json&$select=cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletimoeda,dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)?@moeda='"&CodMoeda&"'&@dataInicial='01-01-2000'&@dataFinalCotacao='12-31-9999'&$top=10000&$filter=tipoBoletim%20eq%20'Fechamento'&$orderby=dataHoraCotacao%20desc&$format=json",
        Query=[select="cotacaoCompra,cotacaoVenda,dataHoraCotacao,tipoBoletim"]])),
    #"Convertido para Tabela" = Record.ToTable(Source),
    #"Linhas Filtradas" = Table.SelectRows(#"Convertido para Tabela", each ([Name] = "value")),
    #"Value Expandido" = Table.ExpandListColumn(#"Linhas Filtradas", "Value")
in
    #"Value Expandido",

    Fonte = Json.Document(Web.Contents("https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/Moedas?$top=100&$format=json&$select=simbolo,nomeFormatado,tipoMoeda")),
    #"Convertido para Tabela" = Record.ToTable(Fonte),
    #"Linhas Filtradas" = Table.SelectRows(#"Convertido para Tabela", each ([Name] = "value")),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Linhas Filtradas",{"Value"}),
    #"Value Expandido" = Table.ExpandListColumn(#"Outras Colunas Removidas", "Value"),
    #"Value Expandido1" = Table.ExpandRecordColumn(#"Value Expandido", "Value", {"nomeFormatado", "simbolo"}, {"nomeFormatado", "simbolo"}),
    #"Função Personalizada Invocada" = Table.AddColumn(#"Value Expandido1", "Consulta1 (2)", each Moeda([simbolo])),
    #"Consulta1 (2) Expandido" = Table.ExpandTableColumn(#"Função Personalizada Invocada", "Consulta1 (2)", {"Value"}, {"Value"}),
    #"Value Expandido2" = Table.ExpandRecordColumn(#"Consulta1 (2) Expandido", "Value", {"cotacaoCompra", "cotacaoVenda", "dataHoraCotacao", "tipoBoletim"}, {"cotacaoCompra", "cotacaoVenda", "dataHoraCotacao", "tipoBoletim"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Value Expandido2",{{"nomeFormatado", type text}, {"simbolo", type text}, {"cotacaoCompra", type number}, {"cotacaoVenda", type number}, {"dataHoraCotacao", type datetime}, {"tipoBoletim", type text}}),
    #"Data Extraída" = Table.TransformColumns(#"Tipo Alterado",{{"dataHoraCotacao", DateTime.Date, type date}})
in
    #"Data Extraída"

[raynok]

Hi There,

I've reclaimed my orginal PowerBI community account Raynok and no longer use rayno.

Just to keep you all in the know I believe this article also played a part as I could get it to work on PowerBI desktop but not in the cloud.  https://community.powerbi.com/t5/Issues/AutoRemovedColumns1-is-accessing-data-sources-that-have-priv...

[Anonymous]

Hi ImkeF,

Thank you for this, I made the changes as advised and it works for PowerBI deskop but still errors in the PowerBI cloud service.
I've read alot of forms and articles, (saw you also commented in a few I read - nice one).

A college has shared an excel document from their one drive for business and shared it with me. The link takes me to the sharePoint destination.

The excel document has been successfully imported with a working custom function to find the addresses from lat's and long's ( 
-22.338304000000001,30.042366999999999) | PowerBI Desktop

I used the web connection to the excel document as per,
https://www.youtube.com/watch?v=t4TzHu8THoA 

This all works perfectly on the PowerBI desktop but keeps failing in the PowerBI cloud service.

From my understanding both sources are external
1.) SharePoint (which is the onedrive saved location)
2.) Google API call to retieve  

The following additional steps where test
1.) Non Staged Query 
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service

This is the error from the PowerBI Cloud Service:
##[Unable to combine data] Section1/Query1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination##

2.) Staged Query as per https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ 
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
This is the error from the PowerBI Cloud Service:
##[Unable to combine data] Section1/Sheet1 (2)/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination##

3.) Excel document was moved to sharedfolder + to "c:\PorjectFolder\data.xlsx | Both results are as follows
Works perfectly on PowerBI desktop | Fails in the PowerBI Cloud Service
This is the error from the PowerBI Cloud Service: Note - A Personal gateway was used and this is a NON Staged data query.

-2147467259 Table: Sheet1.
Underlying error message:	[Unable to combine data] Section1/Sheet1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259

Conclusion:

It all works via PowerBI Deskop and fails on PowerBI cloud.

External sources                                                                 | Failed
Moved source to a local folder via the personal gateway | Failed

[ImkeF]

Hi @Rayno & @Anonymous 

I suggest you read this article: https://social.technet.microsoft.com/Forums/en-US/ca434e2d-88fe-4962-b46a-a1db51e8bd89/feedback-wanted-behind-the-scenes-of-the-data-privacy-firewall?forum=powerquery

give good feedback and try to motivate Ehren to continue his series, where he will hopefully come up with the solution-part as well 😉

Maybe you get some ideas how to tweak your code further, but considering the complexity of the matter, I don't see how I can help you any further in the forum here unfortunately.

[Anonymous]

Thanks @ImkeF for the link to Ehren's series and your willingness to give feedback to the community.

This issue has been extremely frustrating and I've spent countless hours trying to figure it out without success. 

[Anonymous]

Thanks @ImkeF , that link definitely helped.

I always say " We always tell computers what to do" so if it works on PowerBI Desktop(As example we told it to ignore Privacy levels) then there must be someone( a developer  ) that didnt tell the PowerBI cloud service to listen to our Privacy settings within our PowerBI Pbix file with regards to external data sources.

So how we overide it, by telling the PowerBI cloud service what to do, we manually Add the sources to the enterprise gateway,
The enterprise gateway allows one to choose under advance setting the Privacy level which in turns corrects the Privacy level.

So now it works and I've confirmed data changes. 

I believe this needs to be corrected by the PowerBI team.

My idea, they need to add the ability to either take in the Privacy level configured within the pbix file or they need to provide the ability to set the Privacy level under the Data Set setting -> Data Source Credentials.

Otherwise for now, add the source connections to the enterprise gateway and set the Privacy Levels.

Thank you again for the article and for your guidance.
Kind regards,

Rayno  

[Anonymous]

PS, Dont stage the source information when trying the now mentioned workaround. 

It didnt work for me, so I rolled back to the orginal which was to,

Invoke the function within the same Dataquery

Kind regards,

Rayno

[Anonymous]

Logged another incident