schedule refresh failing

Question

I have a powerBI report, in which I am using power query to do data cleaning. I fetch the data from web in a json format and often times the json is nested. I have written a query which looks for any nested json and then expand it's fields into columns. Once this expansion is done, I have another query step which checks for any empty columns in the table and removes them.

These query steps works fine on my powerBI desktop, but when i push the report to powerBI service and schedule refresh, the refresh works fine the first time but on it's second planned refresh it fails saying some columns are missing.

I am not sure, why it is failing in powerBI service when the same steps are working fine in powerBI desktop?

lbendlin · Answer

there could be a version mismatch between the mashup engines on desktop and service (and gateway!), or it can be that some of your transforms are not supported on the service. For example Table.ExpandTableColumns and Table.Combine do the same thing on desktop but behave very differently on service. Try rewriting your power query .

anonymous · Answer

HI&nbsp;Anonymous,
It sounds like you have some query steps with dynamic check fields and remove empty fields based on current value. I think refresh issues should more be related to data structure changes. Power query steps will cache previous data structure if these data structure(field account, name, type) changes that not match with cached one.&nbsp; Query steps who invoked previous fields will be failed due to 'key not match' issues. (query table structure change will affect the generated data mode table and related dax formula and fields)
I'd like to suggest you keep your tables with a static data structure to prevent these type of refresh issues.
Regards,
Xiaoxin Sheng

anonymous · Answer

Hi,Does the catching happen on powerBI service as well? For example, I refresh the data today, would the power query be comparing the result of next refresh with the previously stored data? Is there a way to disable this catching?&nbsp;

anonymous · Answer

HI&nbsp;Anonymous,
Nope, refresh operation need to execute on the same data structure records. BTW, these data structures (data type, column name, fields amount..) will keep the first time your query table processed. 
These fields names who invoked in followings steps not auto changes if you update on previous steps.&nbsp; You need to manually fix them to keep query steps running correctly.
Regards,
Xiaoxin Sheng

anonymous · Answer

Sorry but I am not sure if I am able to understand it correctly. My understanding is in powerquery, if I run the query steps again from step, it overwrites the previously stored data.Usually in first line, I query a data source, is the structure of table returned at first run, preseverd across refreshes? So to say, if I run the query today, I get a table with five columns and some name, is the same structure referenced, when I run the query from start in my next referesh?I am also sharing my code for reference: I have an api url which I hit in the first step it and returns a json data structure. I convert those records into table. As this json is nested, I expand those records into columns and similarly lists to new rows and finally combine into one table. As you can see I have not hard coded any column name, rather I try to remove them based on a condition. These query steps work fine in powerBI desktop but on powerBI service they fail on next refresh.&nbsp;let
    contents = Web.Contents("url", [Timeout=#duration(0,0,10,0)]), 
    source = Json.Document(contents),
    issues = source[issues],
    tble = Table.FromRecords(issues),

//expand list column function
    expandListColumn = (Table, ColumnName) =&gt; Table.ExpandListColumn(
        Table, ColumnName
    ),

//expand record column
    expandRecordColumn = (Table, ColumnName) =&gt; Table.ExpandRecordColumn(
        Table,
        ColumnName,
        Record.FieldNames(
            List.Select(
                Table.Column(Table, ColumnName),
                each Type.Is(Value.Type(_), type record)
            ){0}),
        List.Accumulate(
            Record.FieldNames(
                List.Select(
                    Table.Column(Table, ColumnName),
                    each Type.Is(Value.Type(_), type record)
                ){0}),
            {},
            (s, c) =&gt; List.Combine({s, {Text.Combine({ColumnName, ".", c})}})
        )
    ),

//check for if the column is of type list or record
    listOrRecordColumnNames = (Table) =&gt; List.Accumulate(
        Table.ColumnNames(Table),
        {},
        (s, c) =&gt; s &amp; (
            if List.MatchesAny(
                Table.Column(Table, c),
                each Type.Is(Value.Type(_), type record) or Type.Is(Value.Type(_), type list)
            )
            then {c}
            else {}
        )
    ),

//recursively expand columns
    expandColumn = (Table) =&gt; List.Accumulate(
        listOrRecordColumnNames(Table),
        Table,
        (state, current) =&gt; if List.MatchesAny(
            Table.Column(Table, current),
            each Type.Is(Value.Type(_), type record)
        ) then expandRecordColumn(state, current)
        else expandListColumn(state, current)
    ),

expandColumnsRecursive = (Table) =&gt; (
        if List.IsEmpty(
            listOrRecordColumnNames(Table)
            )
        then Table
        else
            let 
                tempTable = expandColumn(Table),
                NextTable = @expandColumnsRecursive(tempTable)
            in 
                NextTable
    ),
    
    expandedTable = expandColumnsRecursive(tble),

//remove null columns
    removeNullColumns = Table.SelectColumns(
        expandedTable,
        List.Select(
            Table.ColumnNames(expandedTable),
            each List.MatchesAny(
                Table.Column(expandedTable, _),
                each _ &lt;&gt; null
            )
        )
    ),

//remove https column
    removeHttpsColumns = Table.SelectColumns(
        removeNullColumns,
        List.Select(
            Table.ColumnNames(removeNullColumns),
            each List.MatchesAny(
                Table.Column(removeNullColumns, _),
                each not (Text.StartsWith(Text.From(_), "https://"))
            )
        )
    )
in
    removeHttpsColumns&nbsp;

Forum Discussion

schedule refresh failing

6 Replies

Related content

Scheduled pipelines failing

Schedule refresh failed.

Scheduled refresh failing for postgresSQL

Schedule refresh

scheduled refresh failed