New Member

8 months ago

Solved

You can't schedule refresh for this semantic model

Hi,

I'm new in Power BI and I currently facing an issue about this particular semantic model. In settings, it is saying "You can't schedule refresh for this semantic model because the following data sources currently don't support refresh:

Data source for Query1"

Below is the code in advance editor of a particular query:

let

// Parameters

BaseUrl = "https://api.harvestapp.com",

Token = HarvestApiToken,

AccountID = HarvestAccountID,

// ------------------------------------------------

// FIXED: Service-safe Harvest API call

// ------------------------------------------------

GetJson = (RelativePath as text, QueryParameters as record) =>

let

Options = [

Headers = [

Authorization = "Bearer " & Token,

#"Harvest-Account-ID" = AccountID,

#"User-Agent" = "PowerBI"

RelativePath = RelativePath,

Query = QueryParameters

RawData = Web.Contents(BaseUrl, Options),

Json = Json.Document(RawData)

Json,

// Get total pages

GetPageCount = () =>

let

Json = GetJson("/v2/projects", []),

Count = try Json[total_pages] otherwise 1

Count,

// Load a single page

GetPage = (PageIndex as number) =>

let

Json = GetJson("/v2/projects", [page = Text.From(PageIndex)]),

Value = Json[projects]

Value,

// Pagination loop

PageCount = GetPageCount(),

PageIndices = {1..PageCount},

Pages = List.Transform(PageIndices, each GetPage(_)),

Entities = List.Union(Pages),

// Convert list to table

ProjectsTable =

Table.FromList(

Entities,

Splitter.SplitByNothing(),

null,

ExtraValues.Error

// Expand project fields

#"Expanded Projects" =

Table.ExpandRecordColumn(

ProjectsTable, "Column1",

{

"id", "name", "code", "is_active", "is_billable",

"is_fixed_fee", "bill_by", "budget",

"budget_by", "budget_is_monthly",

"notify_when_over_budget",

"over_budget_notification_percentage",

"show_budget_to_all", "created_at",

"updated_at", "starts_on", "ends_on",

"over_budget_notification_date",

"notes", "cost_budget",

"cost_budget_include_expenses",

"hourly_rate", "fee", "client"

}

// Expand client record

#"Expanded Client" =

Table.ExpandRecordColumn(

#"Expanded Projects", "client",

{"id", "name", "currency", "active"},

{"client_id", "client_name", "client_currency", "client_active"}

// Change column types

#"Changed Type" =

Table.TransformColumnTypes(

#"Expanded Client",

{

{"id", Int64.Type},

{"name", type text},

{"code", type text},

{"is_active", type logical},

{"is_billable", type logical},

{"is_fixed_fee", type logical},

{"budget", type number},

{"budget_is_monthly", type logical},

{"notify_when_over_budget", type logical},

{"over_budget_notification_percentage", type number},

{"cost_budget_include_expenses", type logical},

{"hourly_rate", type number},

{"fee", type number},

{"created_at", type datetimezone},

{"updated_at", type datetimezone},

{"starts_on", type date},

{"ends_on", type date},

{"client_name", type text}

}

// Split project code for ProjectType

#"Duplicated Column" =

Table.DuplicateColumn(#"Changed Type", "code", "code - Copy"),

#"Split Column by Delimiter" =

Table.SplitColumn(

#"Duplicated Column", "code - Copy",

Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false),

{"code - Copy.1", "code - Copy.2"}

#"Changed Type1" =

Table.TransformColumnTypes(

#"Split Column by Delimiter",

{{"code - Copy.1", type text}, {"code - Copy.2", type text}}

#"Renamed Columns" =

Table.RenameColumns(

#"Changed Type1",

// Replace project type values

#"Replaced ProjectType" =

Table.ReplaceValue(

#"Renamed Columns",

"S", "Support", Replacer.ReplaceValue, {"ProjectType"}

"PS ", "Professional Services", Replacer.ReplaceText, {"ProjectType"}

"S ", "Support", Replacer.ReplaceText, {"ProjectType"}

"PENDING", "Professional Services", Replacer.ReplaceText, {"ProjectType"}

"PO", "Professional Services", Replacer.ReplaceText, {"ProjectType"}

#"Replaced ProjectType2" =

Table.ReplaceValue(

#"Replaced ProjectType",

"PS", "Professional Services",

Replacer.ReplaceText,

{"ProjectType"}

// Optional filter

#"Filtered Rows" =

Table.SelectRows(

#"Replaced ProjectType2",

each [created_at] > #datetimezone(2019, 6, 1, 0, 0, 0, 10, 0)

)

#"Filtered Rows"

Can you please let us know what we are doing wrong or are we missing anything?

NOTE: We have another semantic model almost similar to this semantic model. However, it is NOT facing the same issue.

Thanks!

Need Help

Mauro89

8 months ago

Hi bebeben10,

I think the issue is with how you're using Web.Contents() with dynamic parameters. Power BI Service can't refresh queries that use Web.Contents() with dynamically constructed URLs unless you follow specific patterns for query folding.

Possible Solution - Use static base URL with dynamic parameters:

Replace your GetJson function with this:

GetJson = (RelativePath as text, QueryParameters as record) =>
    let
        // Build query string manually
        QueryString = Uri.BuildQueryString(QueryParameters),
        FullUrl = BaseUrl & RelativePath & 
                  (if QueryString = "" then "" else "?" & QueryString),
        
        Options = [
            Headers = [
                Authorization = "Bearer " & Token,
                #"Harvest-Account-ID" = AccountID,
                #"User-Agent" = "PowerBI"
            ]
        ],
        RawData = Web.Contents(FullUrl, Options),
        Json = Json.Document(RawData)
    in
        Json,

Better solution - Make credentials recognizable:

Power BI needs to recognize the data source. Try this approach:

GetJson = (RelativePath as text, PageNum as number) =>
    let
        Options = [
            Headers = [
                Authorization = "Bearer " & Token,
                #"Harvest-Account-ID" = AccountID,
                #"User-Agent" = "PowerBI"
            ]
        ],
        FullUrl = "https://api.harvestapp.com" & RelativePath & "?page=" & Number.ToText(PageNum),
        RawData = Web.Contents(FullUrl, Options),
        Json = Json.Document(RawData)
    in
        Json,

Also check: In Power BI Service, go to Settings → Data source credentials and ensure your API token parameter is configured as a dataset parameter with proper credentials set.

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

4 Replies

Mauro89

Super User

8 months ago

Hi bebeben10,

Possible Solution - Use static base URL with dynamic parameters:

Replace your GetJson function with this:

GetJson = (RelativePath as text, QueryParameters as record) =>
    let
        // Build query string manually
        QueryString = Uri.BuildQueryString(QueryParameters),
        FullUrl = BaseUrl & RelativePath & 
                  (if QueryString = "" then "" else "?" & QueryString),
        
        Options = [
            Headers = [
                Authorization = "Bearer " & Token,
                #"Harvest-Account-ID" = AccountID,
                #"User-Agent" = "PowerBI"
            ]
        ],
        RawData = Web.Contents(FullUrl, Options),
        Json = Json.Document(RawData)
    in
        Json,

Better solution - Make credentials recognizable:

Power BI needs to recognize the data source. Try this approach:

GetJson = (RelativePath as text, PageNum as number) =>
    let
        Options = [
            Headers = [
                Authorization = "Bearer " & Token,
                #"Harvest-Account-ID" = AccountID,
                #"User-Agent" = "PowerBI"
            ]
        ],
        FullUrl = "https://api.harvestapp.com" & RelativePath & "?page=" & Number.ToText(PageNum),
        RawData = Web.Contents(FullUrl, Options),
        Json = Json.Document(RawData)
    in
        Json,

Also check: In Power BI Service, go to Settings → Data source credentials and ensure your API token parameter is configured as a dataset parameter with proper credentials set.

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

Royel

Super User

8 months ago

Hi bebeben10 Power BI Service dose not support dynamic parameters in Web.Contents however it works fine in Power BI Desktop.

Instead of using BaseUrl as a variable use it as a literal string.

Update your Web.Contents with this

GetJson = (RelativePath as text, QueryParameters as record) =>
    let
        RawData = Web.Contents(
            "https://api.harvestapp.com", 
            [
                Headers = [
                    Authorization = "Bearer " & Token,
                    #"Harvest-Account-ID" = AccountID,
                    #"User-Agent" = "PowerBI"
                ],
                RelativePath = RelativePath,
                Query = QueryParameters
            ]
        ),
        Json = Json.Document(RawData)
    in
        Json,

Here is other similar solutions from community:

https://community.fabric.microsoft.com/t5/Desktop/Dynamic-datasource-error-with-relativepath/td-p/2933132

Thanks

Anonymous
Not applicable
8 months ago
Hi bebeben10

I wanted to check if you had the opportunity to review the valuable information provided by Royel and Mauro89 . Please feel free to contact us if you have any further questions.

Thank you.
Anonymous
Not applicable
8 months ago
Hi bebeben10

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

Forum Discussion

You can't schedule refresh for this semantic model

4 Replies

Related content

Schedule refresh

Scheduled Semantic Model Refresh fails

Schedule refresh

Facing issue while scheduled refreshing semantic model

Scheduled Refresh Failure