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.

[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