Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Query contains unsupported function. Function name: Web.Contents for web API

Hi, I have following code I am using to get data from WEB API, I am getting error when I tried to schedule refresh in Powerbi Service. I am not sure what needs to be changed in code to get it work....
M Query
Web Contents failed
my_bi_quest's avatar
my_bi_quest
Frequent Visitor
6 years ago

Hi Anonymous.

 

I took a quick look at your query and the problem is definitely on your second one.

Try breaking it like this and let me know if it worked:

 

"...

data= Json.Document(Web.Contents(https://openapi.example.com/,
[
RelativePath="api/reporting-details/v2/prod/views/PurchaseOrders?realm=myrealm&filters=%7B%22createdDateFrom%22%3A%22"& startDateText &"%3A00%3A00Z%22%2C%22createdDateTo%22%3A%22"&endDateText&"%3A59%3A59Z%22%7D"&qry_str,
Headers = [
#"Authorization"="Bearer "&token,
#"Content-Type"="application/json",
#"apiKey"="845RCEJTL63884jlkjaellT77kwnn"
]
])),

..."

 

The bottom idea with this issue is the the Power Bi service doesn't want anything dynamic on the main URL, that's why everything with parameters should go on the Relative Path or the Query sections.

You should always make a call to Web.Contents with only the domain name as the first parameter and then break the rest of the URL within the others parameters of the function.

 

Hope it helps!

MatiasVizzari's avatar
MatiasVizzari
Helper I
6 years ago

Good morning, I am having the same problem but when connecting to some DBF tables from different databases concatenated by a GetData, I understand that I would have to encode the DataSource in some way so that the power bi service recognizes it, but I do not understand how do it, any kind of help would be appreciated, I share the code of GetData and the one of the resulting table, thank you very much

 

(Nlocal)=>
let
    Origen = Excel.Workbook(File.Contents("C:\Users\administrador.ESTANCIAS\Documents\Power BI Desktop\Sucursales.xlsx"), null, true),
    Sheet1_Sheet = Origen{[Item="Sheet1",Kind="Sheet"]}[Data],
    LOCAL = Sheet1_Sheet{Nlocal}[Column9],
    

    Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\LINCEV3\"&LOCAL&"\DBF\;extended properties=dBASE IV",
    [Query="select CLCOD, CLTPO, CLNOM, CLDIR, CLLOC, CLCP, CLCUIT, CLVCODPAG, CLFECHA, CLFING, CLEMAIL from [cli.dbf]"])
in
    Source
let
    Source = Excel.Workbook (File.Contents ("C: \ Users \ administrator.STANCES \ Documents \ Power BI Desktop \ Branches.xlsx"), null, true),
    Sheet1_Sheet = Origin {[Item = "Sheet1", Kind = "Sheet"]} [Data],
    # "Promoted Headers1" = Table.PromoteHeaders (Sheet1_Sheet, [PromoteAllScalars = true]),
    # "Type changed" = Table.TransformColumnTypes (# "Promoted headers1", {{"LOCATION", type text}, {"GROUPING", type any}, {"LOCAL_NAME", type any}, {"LOCAL_NAME_TOTAL", type any}, {"COD_LOCAL", Int64.Type}, {"CONCEPT", type text}, {"SSS", type text}, {"TYPE", type text}, {"LOCAL", type text}, { "Active", Int64.Type}, {"Order", Int64.Type}}),
    # "Custom added" = Table.AddColumn (# "Type changed", "Custom", each GetDataCli ([COD_LOCAL])),
    # "Other columns removed" = Table.SelectColumns (# "Custom added", {"COD_LOCAL", "Custom"}),
    # "Custom expanded" = Table.ExpandTableColumn (# "Other columns removed", "Custom", {"CLCOD", "CLTPO", "CLNOM", "CLDIR", "CLLOC", "CLCP", "CLCUIT" , "CLVCODPAG", "CLFECHA", "CLFING", "CLEMAIL"}, {"CLCOD", "CLTPO", "CLNOM", "CLDIR", "CLLOC", "CLCUIT", "CLVCODPAG" , "CLFECHA", "CLFING", "CLEMAIL"}),
    # "Duplicates removed" = Table.Distinct (# "Custom expanded", {"CLCOD"})
in
    # "Duplicates removed"