cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

schedule refresh failing

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?

6 REPLIES 6
Community Support
Community Support

HI @sharma_mohit,

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.  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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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?

 

HI @sharma_mohit,

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.  You need to manually fix them to keep query steps running correctly.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

 

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) => Table.ExpandListColumn(
        Table, ColumnName
    ), 

    //expand record column
    expandRecordColumn = (Table, ColumnName) => 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) => List.Combine({s, {Text.Combine({ColumnName, ".", c})}})
        )
    ),

    //check for if the column is of type list or record
    listOrRecordColumnNames = (Table) => List.Accumulate(
        Table.ColumnNames(Table),
        {},
        (s, c) => s & (
            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) => List.Accumulate(
        listOrRecordColumnNames(Table),
        Table,
        (state, current) => if List.MatchesAny(
            Table.Column(Table, current),
            each Type.Is(Value.Type(_), type record)
        ) then expandRecordColumn(state, current)
        else expandListColumn(state, current)
    ),

    expandColumnsRecursive = (Table) => (
        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 _ <> 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

 

Hi @sharma_mohit,

Power query tables are the preview of query steps processing results, they do not contain any storage data features. At the first time your code processed, these static table structures will be inputted and invoke in different query steps.
When you save and back to the data model side, these query formulas will be executed and receive data from datasource. (at this step, your table structure are transform generated as data model table fields)
If refreshed data structure cannot be matched with generated data mode table structure, your Dax codes and visual design will be broken due to not match/missed fields.

For power bi service refresh operations, it required your data structure matches with publishing datasets. If they not matched, these refresh operations obviously not trigger and return error messages.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User III
Super User III

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 .

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors