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",
periodToDate="30-06-2021"
],
options=[RelativePath = relpath,Query=quer ]&[Headers =[#"Authorization"= "Basic code=="]],
Source = Json.Document(Web.Contents(url,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"
The problem is that it states in powerbi:
What can i do to workarround this error?
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"
7 Replies
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 workinghttp://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?
Have you tried to use solution suggested in
Setting a scheduled refresh on a Dynamic Data Source in Power BI | by dataninja | DataDrivenInvestorChange the Source of Power BI Datasets Dynamically Using Power Query Parameters - RADACAD
If this post helps, then please consider Accept it as the solution and give it a thumbs up
The first item is almost the same as mine change mine to have a full relative path but it did not have any effect . The second one is more on SQL which is a bit different using a gateway which i am not using
Hi Paul_keijzers ,
If post above doesn't help, we suggest you go to https://powerbi.microsoft.com/en-us/support/ ,scroll down and click "CREATE SUPPORT TICKET" for further help if you are a pro user.
Best Regards,
Eyelyn QinIn 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 ,
Glad to hear that your issue has been resolved! And thanks for sharing the solution .More people could benefit from it.😀
Best Regards,
Eyelyn Qin
