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

[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