Handle table with different amount of columns.

[nicolasvc]

I have an excel in the number of columns are changing, so the first time I worked with it, I had no problems. But now the excel has one less column and I get the error "The column 'Column10' of the table wasn't found." And it is because the file now has 9 columns, this happens in Power Query in the Type Changed step. Is there a way to handle this problem automatically? I can't delete the column in the editor every time the file changes.

[v-jingzhang · Accepted solution]

Hi @nicolasvc 

Do you need to use data in this column in your report? If you don't need it, you could add a step to select all other columns except this column to remove it from the table. After this step, change data types for remaining columns maunally. 

= Table.SelectColumns(Source,{"Column1", "Column2", "Column3", "Column4"})

Or

= Table.SelectColumns(Source,List.RemoveItems(Table.ColumnNames(Source),{"Column10"}))

If you don't want to remove Column10 when it exists, you can append an empty table which has a column called Column10 to source table before Changed Type step. In this way, if original data source table doesn't have Column10, the empty table will append null values to Column10 thus Column10 could be referred to in its following steps. See Avoid refresh errors with missing columns in Power BI/Power Query - YouTube

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpMBBK5uUDC0MAASJoAyVidaCUjPHLGeORMgOykJFQ5U6icKRY5Y5BcLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column15 = _t]),
    EmptyTable = #table({"Column10"},{}),
    Combine = Table.Combine({Source, EmptyTable}),
    #"Changed Type" = Table.TransformColumnTypes(Combine,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column10", Int64.Type}})
in
    #"Changed Type"

There are some more methods you can try:

Dynamically add missing columns in Power Query - YouTube

Chris Webb's BI Blog: Handling Added Or Missing Columns In Power Query Chris Webb's BI Blog

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[mahoneypat]

You can add the optional MissingField.Ignore parameter in your Table.TransformColumnTypes function in the Formula Bar.

Pat