Forum Discussion
Solved
Issue with refreshing dataset in Powerbi
I got a issue with my code when i use this code: let
url= "https://tenant.yoobi.nl/api/v1/",
relpath="reportDirectGet/TABLEAU",
quer=
[
periodFromDate="01-06-2021",
...
In the end this is the code which is working:
let quer= "?periodFromDate=" & startdate & "&periodToDate="& enddate, options=[Headers =[#"Authorization"= "Basic key=="]], Source = Json.Document(Web.Contents("https://tenant.yoobi.nl/api/v1/reportDirectGet/TABLEAU" & quer,options)), jsondata = Source[jsondata], #"Converted to Table" = Table.FromList(jsondata, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "reports"}}), #"Expanded reports" = Table.ExpandRecordColumn(#"Renamed Columns", "reports", {"lastname", "infix", "initials", "firstname", "gender", "bsn", "title", "departmentname", "departmentcode", "email", "username", "dateofbirth", "employeenumber", "employeestate", "dateregistration", "dateretirement", "dateemployed", "jobname", "jirauser", "gmail", "employeeabbr", "contractnr", "fte", "contracttype", "contractdatestart", "contractdateend", "datum", "uur", "opmerkingen", "soortuur", "bijgewerkt_tot", "acc_datum", "projectmanager", "projectname", "startdate", "enddate", "code", "projectstate", "projectextrastate", "projectclassification", "projectdescription", "activityname", "startdateactivity", "enddateactivity", "activitystate", "ledgeraccount", "isbillable", "customercode", "declarationtypeactivitylist", "declarationtypeprojectlist", "projectbudgettype", "projectbudget", "activitybudget", "projectkeywords", "activitykeywords"}, {"lastname", "infix", "initials", "firstname", "gender", "bsn", "title", "departmentname", "departmentcode", "email", "username", "dateofbirth", "employeenumber", "employeestate", "dateregistration", "dateretirement", "dateemployed", "jobname", "jirauser", "gmail", "employeeabbr", "contractnr", "fte", "contracttype", "contractdatestart", "contractdateend", "datum", "uur", "opmerkingen", "soortuur", "bijgewerkt_tot", "acc_datum", "projectmanager", "projectname", "startdate", "enddate", "code", "projectstate", "projectextrastate", "projectclassification", "projectdescription", "activityname", "startdateactivity", "enddateactivity", "activitystate", "ledgeraccount", "isbillable", "customercode", "declarationtypeactivitylist", "declarationtypeprojectlist", "projectbudgettype", "projectbudget", "activitybudget", "projectkeywords", "activitykeywords"}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded reports", {{"datum", type date}}, "nl-NL"), #"Removed Columns" = Table.RemoveColumns(#"Changed Type with Locale",{"initials", "gender", "bsn", "title", "departmentname", "departmentcode", "dateofbirth", "employeenumber", "employeestate", "dateregistration", "dateretirement", "dateemployed", "jobname", "jirauser", "gmail", "employeeabbr", "contractnr", "fte", "contracttype", "contractdatestart", "contractdateend", "soortuur", "bijgewerkt_tot", "acc_datum", "projectmanager", "startdate", "enddate", "projectclassification", "projectdescription", "startdateactivity", "enddateactivity", "activitystate", "ledgeraccount", "isbillable", "customercode", "declarationtypeactivitylist", "declarationtypeprojectlist", "projectbudgettype", "activitybudget", "projectkeywords", "activitykeywords"}) in #"Removed Columns"
Hi Paul_keijzers ,
It is because Power BI cannot validate the URL of the query. You have to make the URL static and use the Relative Path
Just as the official document said:
You will have to look at the link and have to change the underlying query. And here is a way to get it working
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
Below are similar threads, hopes it could help you a little:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Eyelyn,
I was a week away sorry for late reply. it is still not working tried the following:
let
relpath="/api/v1/reportDirectGet/TABLEAU",
quer=
[
periodFromDate="01-06-2021",
periodToDate="30-06-2021"
],
options=[RelativePath = relpath,Query=quer ]&[Headers =[#"Authorization"= "Basic key=="]],
Source = Json.Document(Web.Contents("https://tenant.yoobi.nl",options)),
jsondata = Source[jsondata],
#"Converted to Table" = Table.FromList(jsondata, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "reports"}}),
#"Expanded reports" = Table.ExpandRecordColumn(#"Renamed Columns", "reports", {"lastname", "infix", "initials", "firstname", "gender", "bsn", "title", "departmentname", "departmentcode", "email", "username", "dateofbirth", "employeenumber", "employeestate", "dateregistration", "dateretirement", "dateemployed", "jobname", "jirauser", "gmail", "employeeabbr", "contractnr", "fte", "contracttype", "contractdatestart", "contractdateend", "datum", "uur", "opmerkingen", "soortuur", "bijgewerkt_tot", "acc_datum", "projectmanager", "projectname", "startdate", "enddate", "code", "projectstate", "projectextrastate", "projectclassification", "projectdescription", "activityname", "startdateactivity", "enddateactivity", "activitystate", "ledgeraccount", "isbillable", "customercode", "declarationtypeactivitylist", "declarationtypeprojectlist", "projectbudgettype", "projectbudget", "activitybudget", "projectkeywords", "activitykeywords"}, {"lastname", "infix", "initials", "firstname", "gender", "bsn", "title", "departmentname", "departmentcode", "email", "username", "dateofbirth", "employeenumber", "employeestate", "dateregistration", "dateretirement", "dateemployed", "jobname", "jirauser", "gmail", "employeeabbr", "contractnr", "fte", "contracttype", "contractdatestart", "contractdateend", "datum", "uur", "opmerkingen", "soortuur", "bijgewerkt_tot", "acc_datum", "projectmanager", "projectname", "startdate", "enddate", "code", "projectstate", "projectextrastate", "projectclassification", "projectdescription", "activityname", "startdateactivity", "enddateactivity", "activitystate", "ledgeraccount", "isbillable", "customercode", "declarationtypeactivitylist", "declarationtypeprojectlist", "projectbudgettype", "projectbudget", "activitybudget", "projectkeywords", "activitykeywords"}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded reports", {{"datum", type date}}, "nl-NL"),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type with Locale",{"initials", "gender", "bsn", "title", "departmentname", "departmentcode", "dateofbirth", "employeenumber", "employeestate", "dateregistration", "dateretirement", "dateemployed", "jobname", "jirauser", "gmail", "employeeabbr", "contractnr", "fte", "contracttype", "contractdatestart", "contractdateend", "soortuur", "bijgewerkt_tot", "acc_datum", "projectmanager", "startdate", "enddate", "projectclassification", "projectdescription", "startdateactivity", "enddateactivity", "activitystate", "ledgeraccount", "isbillable", "customercode", "declarationtypeactivitylist", "declarationtypeprojectlist", "projectbudgettype", "activitybudget", "projectkeywords", "activitykeywords"})
in
#"Removed Columns"
In my believe it is now the way it is decribed? If i just make it a hard url then it works but that is not adjustable to my needs.
Did i miss something?