New column being added to the data source I'm connected to - Connection will break. How do I fix?

[whatisdata96]

Hi all - title says it all. I am trying to figure out what to do when a new column is added into a data source/table that I'm connecting to.

I know that when that happens, I will get an error in Power Query when I refresh. What can I do to fix this once it happens? Do I have to do something/change something in the advanced editor? Would appreciate any help.

Thanks!

[Anonymous]

Is this DirectQuery or Import mode?

[ImkeF]

Hi @whatisdata96 ,
So you don't have to worry then with your current query. New columns should be picked up just alright.

[ImkeF]

Hi @whatisdata96 ,
this looks as if your query could break if certains columns are NOT included in your new data.
What does the error message exactly say?

[whatisdata96]

So actually, there is no error message yet. I'm anticipating a new column being added to a table I'm connecting to. I know that when source data structure changes at all, it comes up as an error in PQ.

I don't write custom M functions often enough to know what to add into the advanced editor to account for an extra column. I'm sure it can't be too complicated.

[whatisdata96]

Here is my source code in the advanced editor:

let
Source = Excel.Workbook(File.Contents("C:\Users\ron\Desktop\Data Sources\Volume\SL Data Sheet sht.xlsx"), null, true),
#"Sheet 1_Sheet" = Source{[Item="Sheet 1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sheet 1_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Day of Week", type text}, {"Calls Abandoned", Int64.Type}, {"Call Errors", Int64.Type}, {"Calls Handled", Int64.Type}, {"Calls Offered", Int64.Type}, {"SL", type number}, {"SL Goal", type number}, {"ASA", type number}, {"ASA Goal", type number}, {"SL20cnt", Int64.Type}, {"SL120cnt", Int64.Type}, {"SL300cnt", Int64.Type}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Bottom Rows",{{"ASA", type duration}, {"ASA Goal", type duration}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Duration.TotalSeconds([ASA])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Duration.TotalSeconds([ASA Goal])),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "ASA (Sec)"}, {"Custom.1", "ASA (Goal)"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"ASA (Sec)", type number}, {"ASA (Goal)", type number}})
in
#"Changed Type2"



There will be a new column called 'ACT'

[ImkeF]

Hi @whatisdata96 ,
it is not necessarily the case that newly added columns in the source data will break the refresh.
There must be something in your transformation steps that causes it.
So please paste the M-code of your query here for further analysis. You can retrieve it by going to the advanced editor.