Issue with refreshing dataset in Powerbi

[Paul_keijzers]

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?

[Anonymous]

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

[Paul_keijzers · Accepted 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"

[Anonymous]

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

[Anonymous]

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:

https://community.powerbi.com/t5/Power-Query/Dynamic-data-sources-aren-t-refreshed-in-the-Power-BI-s...

https://community.powerbi.com/t5/Service/Help-Dataset-includes-a-dynamic-data-source-error/m-p/17820...

https://community.powerbi.com/t5/Power-Query/REST-API-Dynamic-Datasource-aren-t-refreshed/m-p/181165...

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.

[Paul_keijzers]

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? 

[arvindsingh802]

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

[Paul_keijzers]

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