Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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 ,
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 Qin
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 ,
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
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?
Have you tried to use solution suggested in
Setting a scheduled refresh on a Dynamic Data Source in Power BI | by dataninja | DataDrivenInvestor
Change 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.