Power BI model refresh fails due to deleted or renamed columns in upstream.

[Jagruthi_Kesha]

Hi,

I'm encountering an issue where the Power BI scheduled refresh fails in the Service whenever columns are deleted or renamed in the upstream (Snowflake) source. I'm using Import mode to load the data, and my Power Query script is structured as follows:

let
Source = Snowflake.Databases(Snowflakeurl,"WH_XS"),
ABC_Database = Source{[Name="ABC",Kind="Database"]}[Data],
XYZ_Schema = ABC_Database{[Name="XYZ",Kind="Schema"]}[Data],
SALES_Table = XYZ_Schema{[Name="SALES",Kind="Table"]}[Data]
in
SALES_Table 

How can I handle schema changes gracefully - such as column deletions or renames - so that scheduled refreshes don't break in the Power BI Service?


Thanks for the help!

[Omid_Motamedise · Accepted solution]

Hi @Jagruthi_Kesha 

You add a new step as following to handle it.

let
    Source = Snowflake.Databases(Snowflakeurl,"WH_XS"),
    ABC_Database = Source{[Name="ABC",Kind="Database"]}[Data],
    XYZ_Schema = ABC_Database{[Name="XYZ",Kind="Schema"]}[Data],
    SALES_Table = XYZ_Schema{[Name="SALES",Kind="Table"]}[Data],
    Untyped = Value.ReplaceType(SALES_Table, type table [ ])
in
    Untyped

[Jagruthi_Kesha]

Hi, 
I’ve tried several different approaches, but none of them worked. Untype also isn’t supported on my Power BI desktop.

Approach 1:
let
Source = Snowflake.Databases(snowflakeurl, SnowflakeImport),
ABC_Database = Source{[Name="ABC", Kind="Database"]}[Data],
XYZ_Schema = ABC_Database{[Name="XYZ", Kind="Schema"]}[Data],
SALES_TABLE = XYZ_Schema{[Name="SALES", Kind="View"]}[Data],
#"Renamed Columns" = Table.RenameColumns(SALES_TABLE,{{"A_B_C", "Abc"},{"B_A_C", "Bac"}},MissingField.Ignore)
in
#"Renamed Columns"

Approach 2:
let
Source = Snowflake.Databases(snowflakeurl, SnowflakeImport),
ABC_Database = Source{[Name="ABC", Kind="Database"]}[Data],
XYZ_Schema = ABC_Database{[Name="XYZ", Kind="Schema"]}[Data],
SALES_TABLE = XYZ_Schema{[Name="SALES", Kind="View"]}[Data],
NoFolding = Table.TransformColumnNames(SALES_TABLE, each _),
#"Renamed Columns" = Table.RenameColumns(NoFolding,{{"A_B_C", "Abc"},{"B_A_C", "Bac"}},
MissingField.Ignore)
in
#"Renamed Columns"

Approach 3:
let
Source = Snowflake.Databases(snowflakeurl, SnowflakeImport),
ABC_Database = Source{[Name="ABC", Kind="Database"]}[Data],
XYZ_Schema = ABC_Database{[Name="XYZ", Kind="Schema"]}[Data],
SALES_TABLE = XYZ_Schema{[Name="SALES", Kind="View"]}[Data],
#"Renamed Columns" = Table.RenameColumns(SALES_TABLE,{{"A_B_C", "Abc"},{"B_A_C", "Bac"}},MissingField.Ignore)
in
#"Renamed Columns"

Approach 4:
let
Source = Snowflake.Databases(snowflakeurl, SnowflakeImport, [CreateNavigationProperties=false]),
ABC_Database = Source{[Name="ABC", Kind="Database"]}[Data],
XYZ_Schema = ABC_Database{[Name="XYZ", Kind="Schema"]}[Data],
SALES_TABLE = XYZ_Schema{[Name="SALES", Kind="View"]}[Data],
LiveColumns = Table.ColumnNames(SALES_TABLE),
#"Selected Columns" = Table.SelectColumns(SALES_TABLE, LiveColumns),
#"Renamed Columns" = Table.RenameColumns(#"Selected Columns",{{"A_B_C", "Abc"},{"B_A_C", "Bac"}},MissingField.Ignore)
in
#"Renamed Columns"

[V-yubandi-msft]

Hi @Jagruthi_Kesha ,

Thank you for testing. I see you’ve tried multiple Power Query methods, but the refresh issue persists.

It’s important to note that Power BI Import mode relies on a stable schema. If columns are renamed or deleted in Snowflake, the scheduled refresh will fail even with defensive M functions.

You can refer to Microsoft’s document here: Data refresh in Power BI - Power BI | Microsoft Learn

The best solution is to address schema drift upstream. Creating a stable view in Snowflake with consistent column names and connecting Power BI to that view will help prevent refresh failures when the underlying table changes.

I hope this clarifies the situation. If I’ve misunderstood any part of your situation, please let us know.

[V-yubandi-msft]

Hi @Jagruthi_Kesha ,

Could you let us know if your issue has been resolved, or if you need any additional information or clarification? We are happy to help.

Thank you.

[V-yubandi-msft]

Hi @Jagruthi_Kesha ,

May I know if your issue has been resolved, or if you still need any additional details? Please let us know if you need any further assistance.
 

Thank you.

[V-yubandi-msft · Accepted solution]

Hi @Jagruthi_Kesha ,

Thank you for reaching out to the Fabric Community. To add to @Omid_Motamedise  response, here’s a note for those using the Value.ReplaceType method  when a table becomes untyped, Power Query will stop enforcing column types.
As a result, if your data changes, later steps that depend on specific columns or data types could fail. To maintain some type safety while allowing for schema changes, you can use Table.SelectColumns together with List. Intersect to dynamically select only the columns present in your source data.

Thanks for your quick response @Omid_Motamedise .

Regards,

Yugandhar.

[Jagruthi_Kesha]

Hi @V-yubandi-msft @Omid_Motamedise 

I tried updating the query below, but the Power BI refresh still failed after a column was dropped from the Sales table in Snowflake. Can you tell me if I’m missing something?

let
Source = Snowflake.Databases(snowflakeurl, SnowflakeImport),
ABC_Database = Source{[Name="ABC",Kind="Database"]}[Data],
XYZ_Schema = ABC_Database{[Name="XYZ",Kind="Schema"]}[Data],
SALES_TABLE = JOB_Schema{[Name="SALES",Kind="View"]}[Data],
ExistingColumns = Table.ColumnNames(SALES_TABLE),
DesiredRenames = {{"SALES_AMOUNT", "Sales Amount"}},
ValidRenames = List.Select(DesiredRenames, each List.Contains(ExistingColumns, _{0})),
RenamedTable = Table.RenameColumns(SALES_TABLE, ValidRenames, MissingField.Ignore)
in
RenamedTable

[V-yubandi-msft]

Thank you for sharing the updated query. I noticed that you defined XYZ_Schema earlier, but later referenced JOB_Schema here:

SALES_TABLE = JOB_Schema{[Name="SALES",Kind="View"]}[Data]

If that wasn’t intentional, it might need to reference the same schema as before:

SALES_TABLE = XYZ_Schema{[Name="SALES",Kind="View"]}[Data]

Also, even if you use MissingField.Ignore, the refresh can still fail if another step or the dataset model is referencing the dropped column. Please check if the removed column is used elsewhere in the query steps, measures, relationships, or visuals.

Thanks....

[Omid_Motamedise · Accepted solution]

Hi @Jagruthi_Kesha 

You add a new step as following to handle it.

let
    Source = Snowflake.Databases(Snowflakeurl,"WH_XS"),
    ABC_Database = Source{[Name="ABC",Kind="Database"]}[Data],
    XYZ_Schema = ABC_Database{[Name="XYZ",Kind="Schema"]}[Data],
    SALES_Table = XYZ_Schema{[Name="SALES",Kind="Table"]}[Data],
    Untyped = Value.ReplaceType(SALES_Table, type table [ ])
in
    Untyped