Dynamic data sources aren't refreshed in the Power BI service

[Anonymous]

Hello,

I have problem with schedule refresh with dynamic data sources in the Power BI service.

Here is my query

(URL) as table =>

let
    Source = Json.Document(Web.Contents(URL)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    CheckEmpty = if Table.IsEmpty(Source) then #"Expanded Column1"(type table[Column1 = [_id = any]],{}) else Source,

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "meta/rootUuid", "meta/deprecatedID", "formhub/uuid", "start", "end", "today", "visit_date", "Project_name", "Partner", "Province", "District", "Subdistrict", "Village", "Person/Person_Complete", "Person/Position_Complete", "Person1/Name_informant1", "Person1/Position_informant1", "Person1/Name_informant2", "Person1/Position_informant2", "Theme_group/Theme", "Theme_group/Subtheme", "Theme_group/Cross_theme", "Theme_group/Common_approach1", "Theme_group/Common_approach2", "Name_Activity", "activity_22/act22_1", "activity_22/act22_1_2", "activity_22/act22_1_1", "activity_22/act22_1_3", "activity_22/act22_2", "activity_22/act22_2_2", "activity_22/act22_2_1", "activity_22/act22_2_3", "activity_22/act22_3", "activity_22/act22_3_1", "activity_22/act22_3_3", "activity_22/act22_4", "activity_22/act22_4_1", "activity_22/act22_4_3", "activity_22/act22_5", "activity_22/act22_5_2", "activity_22/act22_6", "activity_22/act22_6_2", "activity_22/act22_7", "activity_22/act22_7_2", "activity_22/act22_7_1", "activity_22/act22_7_3", "activity_22/act22_8", "activity_22/act22_8_2", "activity_22/act22_8_1", "activity_22/act22_8_3", "activity_22/act22_9", "activity_22/act22_9_2", "activity_22/act22_10", "activity_22/act22_10_2", "activity_22/act22_11", "activity_22/act22_11_2", "activity_22/act22_11_1", "activity_22/act22_11_3", "activity_22/act22_12", "activity_22/act22_12_2", "activity_22/act22_12_1", "activity_22/act22_12_3", "action_plan", "comment", "sign/sign_name", "sign/sign_draw", "act22_1num", "act22_2num", "act22_3num", "act22_4num", "act22_5num", "act22_6num", "act22_7num", "act22_8num", "act22_9num", "act22_10num", "act22_11num", "act22_12num", "act22_1denum", "act22_2denum", "act22_3denum", "act22_4denum", "act22_5denum", "act22_6denum", "act22_7denum", "act22_8denum", "act22_9denum", "act22_10denum", "act22_11denum", "act22_12denum", "num_qb", "denom_qb", "qb_score", "status_qb", "__version__", "meta/instanceID", "_xform_id_string", "_uuid", "_attachments", "_status", "_geolocation", "_submission_time", "_tags", "_notes", "_validation_status", "_submitted_by"}, {"_id", "meta/rootUuid", "meta/deprecatedID", "formhub/uuid", "start", "end", "today", "visit_date", "Project_name", "Partner", "Province", "District", "Subdistrict", "Village", "Person/Person_Complete", "Person/Position_Complete", "Person1/Name_informant1", "Person1/Position_informant1", "Person1/Name_informant2", "Person1/Position_informant2", "Theme_group/Theme", "Theme_group/Subtheme", "Theme_group/Cross_theme", "Theme_group/Common_approach1", "Theme_group/Common_approach2", "Name_Activity", "activity_22/act22_1", "activity_22/act22_1_2", "activity_22/act22_1_1", "activity_22/act22_1_3", "activity_22/act22_2", "activity_22/act22_2_2", "activity_22/act22_2_1", "activity_22/act22_2_3", "activity_22/act22_3", "activity_22/act22_3_1", "activity_22/act22_3_3", "activity_22/act22_4", "activity_22/act22_4_1", "activity_22/act22_4_3", "activity_22/act22_5", "activity_22/act22_5_2", "activity_22/act22_6", "activity_22/act22_6_2", "activity_22/act22_7", "activity_22/act22_7_2", "activity_22/act22_7_1", "activity_22/act22_7_3", "activity_22/act22_8", "activity_22/act22_8_2", "activity_22/act22_8_1", "activity_22/act22_8_3", "activity_22/act22_9", "activity_22/act22_9_2", "activity_22/act22_10", "activity_22/act22_10_2", "activity_22/act22_11", "activity_22/act22_11_2", "activity_22/act22_11_1", "activity_22/act22_11_3", "activity_22/act22_12", "activity_22/act22_12_2", "activity_22/act22_12_1", "activity_22/act22_12_3", "action_plan", "comment", "sign/sign_name", "sign/sign_draw", "act22_1num", "act22_2num", "act22_3num", "act22_4num", "act22_5num", "act22_6num", "act22_7num", "act22_8num", "act22_9num", "act22_10num", "act22_11num", "act22_12num", "act22_1denum", "act22_2denum", "act22_3denum", "act22_4denum", "act22_5denum", "act22_6denum", "act22_7denum", "act22_8denum", "act22_9denum", "act22_10denum", "act22_11denum", "act22_12denum", "num_qb", "denom_qb", "qb_score", "status_qb", "__version__", "meta/instanceID", "_xform_id_string", "_uuid", "_attachments", "_status", "_geolocation", "_submission_time", "_tags", "_notes", "_validation_status", "_submitted_by"}),
    #"Split Column" = Table.SplitColumn(#"Expanded Column1", "_geolocation", each _, {"_geolocation.0", "_geolocation.1"}),
    #"Expanded _validation_status" = Table.ExpandRecordColumn(#"Split Column", "_validation_status", {}, {}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded _validation_status",{{"_id", Int64.Type}, {"meta/rootUuid", type text}, {"meta/deprecatedID", type text}, {"formhub/uuid", type text}, {"start", type datetimezone}, {"end", type datetimezone}, {"today", type date}, {"visit_date", type date}, {"Project_name", type text}, {"Partner", type text}, {"Province", type text}, {"District", type text}, {"Subdistrict", type text}, {"Village", type text}, {"Person/Person_Complete", type text}, {"Person/Position_Complete", type text}, {"Person1/Name_informant1", type text}, {"Person1/Position_informant1", type text}, {"Person1/Name_informant2", type text}, {"Person1/Position_informant2", type text}, {"Theme_group/Theme", type text}, {"Theme_group/Subtheme", type text}, {"Theme_group/Cross_theme", type text}, {"Theme_group/Common_approach1", type text}, {"Theme_group/Common_approach2", type text}, {"Name_Activity", type text}, {"activity_22/act22_1", type text}, {"activity_22/act22_1_2", type text}, {"activity_22/act22_1_1", type text}, {"activity_22/act22_1_3", type text}, {"activity_22/act22_2", type text}, {"activity_22/act22_2_2", type text}, {"activity_22/act22_2_1", type text}, {"activity_22/act22_2_3", type text}, {"activity_22/act22_3", type text}, {"activity_22/act22_3_1", type text}, {"activity_22/act22_3_3", type any}, {"activity_22/act22_4", type text}, {"activity_22/act22_4_1", type text}, {"activity_22/act22_4_3", type any}, {"activity_22/act22_5", type text}, {"activity_22/act22_5_2", type text}, {"activity_22/act22_6", type text}, {"activity_22/act22_6_2", type text}, {"activity_22/act22_7", type text}, {"activity_22/act22_7_2", type text}, {"activity_22/act22_7_1", type text}, {"activity_22/act22_7_3", type text}, {"activity_22/act22_8", type text}, {"activity_22/act22_8_2", type text}, {"activity_22/act22_8_1", type text}, {"activity_22/act22_8_3", type text}, {"activity_22/act22_9", type text}, {"activity_22/act22_9_2", type text}, {"activity_22/act22_10", type text}, {"activity_22/act22_10_2", type text}, {"activity_22/act22_11", type text}, {"activity_22/act22_11_2", type text}, {"activity_22/act22_11_1", type text}, {"activity_22/act22_11_3", type text}, {"activity_22/act22_12", type text}, {"activity_22/act22_12_2", type text}, {"activity_22/act22_12_1", type text}, {"activity_22/act22_12_3", type text}, {"action_plan", type any}, {"comment", type text}, {"sign/sign_name", type text}, {"sign/sign_draw", type text}, {"act22_1num", Int64.Type}, {"act22_2num", Int64.Type}, {"act22_3num", Int64.Type}, {"act22_4num", Int64.Type}, {"act22_5num", Int64.Type}, {"act22_6num", Int64.Type}, {"act22_7num", Int64.Type}, {"act22_8num", Int64.Type}, {"act22_9num", Int64.Type}, {"act22_10num", Int64.Type}, {"act22_11num", Int64.Type}, {"act22_12num", Int64.Type}, {"act22_1denum", Int64.Type}, {"act22_2denum", Int64.Type}, {"act22_3denum", Int64.Type}, {"act22_4denum", Int64.Type}, {"act22_5denum", Int64.Type}, {"act22_6denum", Int64.Type}, {"act22_7denum", Int64.Type}, {"act22_8denum", Int64.Type}, {"act22_9denum", Int64.Type}, {"act22_10denum", Int64.Type}, {"act22_11denum", Int64.Type}, {"act22_12denum", Int64.Type}, {"num_qb", Int64.Type}, {"denom_qb", Int64.Type}, {"qb_score", Int64.Type}, {"status_qb", type text}, {"__version__", type text}, {"meta/instanceID", type text}, {"_xform_id_string", type text}, {"_uuid", type text}, {"_attachments", type any}, {"_status", type text}, {"_geolocation.0", type any}, {"_geolocation.1", type any}, {"_submission_time", type datetime}, {"_tags", type any}, {"_notes", type any}, {"_submitted_by", type any}})
in
    #"Changed Type"

Please help me 

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description, you use the "Url" parameter in your Web.Contents() function .

And when you refresh in the Power BI Service , you get the error code “You can’t schedule refresh for this dataset because one or more sources currently don’t support refresh.”

For this reason , Power BI Service do not support the dynamic sources when we refresh .

We can not use the parameter in Web.Contents() function , you need to modify your M code like this:

And for how to modify and what is the dynamic source in Power BI Service , you can refer to this document:
Dynamic Web.Contents() and Power BI Refresh Errors – Data Inspirations

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[GilbertQ]

Hi @Anonymous 

You have to have a static value to refresh datasets in Power BI in order to the data sources to be setup.

Have a look at this blog post which might help: Power Query Errors: Please Rebuild This Data Combination (excelguru.ca)