Define and invoke function within query

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.

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

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]])),

---

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