Append queries dynamically

[NISH72]

Hi I have appended 4 different tables in one new table in query editor(power bi). The data source is Excel. But a user deleted a column in original data source in excel and now after refresh the appended new table is showing error. Is there a way in which even if user deletes a column in excel . The appended table or query should not be affected Thanks

[MattAllington]

Yes. Chris Webb wrote about it a few years ago. https://blog.crossjoin.co.uk/2015/02/26/handling-added-or-missing-columns-in-power-query/

[NISH72]

Hi. Thanks for reply. Actually I have already removed the column I don't want in query editor. But the removed column was deleted from data source in excel and still this problem is there. Thanks

[kcantor]

@NISH72 

In your query, click on the step where you removed the column. This is probably where your error is occuring. Edit this in the formula bar in the query editor and very carefully remove the column name from the M. Then hit enter. The problem is, as Query Editor sees it, the column you are trying to remove doesn't exist.

As and example, here is a snip from one of my date tables where I removed a handful of columns:

#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"DateInt", "YearKey", "HalfYearKey", "QuarterKey", "MonthKey", "MonthOfYear", "QuarterOfYear", "DayOfWeekMon", "DayOfWeekSun", "WeekOfYearISO"})

If the column "HalfYearKey" was removed from my source, this would break this step. I would remove that column, the quotes, and the comma to leave this version so that Power Query would not look for that column to remove:

#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"DateInt", "YearKey", "QuarterKey", "MonthKey", "MonthOfYear", "QuarterOfYear", "DayOfWeekMon", "DayOfWeekSun", "WeekOfYearISO"})

Please let me know if I have misunderstood your request and I will try again. If possible, share your query from the Advanced Editor and let us know which column is no longer there.