Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
You can add the optional MissingField.Ignore parameter in your Table.TransformColumnTypes function in the Formula Bar.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |