Adding new column to data source

[Shawry]

Hi, 

I've built a dashboard, but need to add another column (in column AC) to the source file. I've tried adding the column to the end, however, when I refresh the PowerBI workbook, the column is being dropped off. I can't seem to find where to prevent the column being dropped off/removed. I've checked and can't see the #Removed Other Columns1 step removing this column?

Here is the following M code. I've named the column "Test" and it should be index 29 or column 29. 

let
    Source = Folder.Files("S:\Distribution\Reporting\Contact Centre\PowerBI Contact Centre Dashboard\Contact Centre Weekly Data"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (10)", each #"Transform File (10)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (10)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (10)", Table.ColumnNames(#"Transform File (10)"(#"Sample File (10)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Queue Summary and Detail", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",12),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"OFF", Int64.Type}, {"-- ANS --", type any}, {"Column6", type text}, {"-- ABD --", type any}, {"Column8", type text}, {"ASA", type time}, {"ANS SVC #(lf)LVL%", Percentage.Type}, {"AVG    TALK", type time}, {"AVG #(lf)HOLD", type time}, {"AVG #(lf)ACW", type time}, {"AVG HAND", type time}, {"FLOW#(lf)OUT#(lf)#", Int64.Type}, {"HOLD#(lf)#", type text}, {"ANS#(lf)40", Int64.Type}, {"ANS#(lf)60", Int64.Type}, {"ANS#(lf)90", Int64.Type}, {"ANS#(lf)120", Int64.Type}, {"ANS#(lf)300", Int64.Type}, {"ANS#(lf)600", Int64.Type}, {"ABD#(lf)40", Int64.Type}, {"ABD#(lf)60", Int64.Type}, {"ABD#(lf)90", Int64.Type}, {"ABD#(lf)120", Int64.Type}, {"ABD#(lf)300", Int64.Type}, {"ABD#(lf)600", Int64.Type}}),
    #"Removed Top Rows1" = Table.Skip(#"Changed Type1",4),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Top Rows1",{{"Column8", "Abandonment Rate %"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns2",{"Column2", "Column3", "Column6", "ANS#(lf)40", "ANS#(lf)60", "ANS#(lf)90", "ANS#(lf)120", "ANS#(lf)300", "ANS#(lf)600", "ABD#(lf)40", "ABD#(lf)60", "ABD#(lf)90", "ABD#(lf)120", "ABD#(lf)300", "ABD#(lf)600"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1", "Date"}, {"OFF", "Calls Offered"}, {"-- ANS --", "Calls Answered"}, {"-- ABD --", "Calls Abandoned"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Abandonment Rate %", Percentage.Type}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type3", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

Is there any easy to approach to add another column? How do I tackle this? Other posts say it should be included automatically. But it's not. 

[Anonymous]

Hi @Shawry ,

Regarding your question, does the new column exist in your original data source file? If not, please move it to the original data source file. Generally, after clicking the refresh button, the new column will appear automatically.

[Shawry]

Hi @Anonymous 

Thanks for you reply. 

Yes, I have included the column in my original data source, and you can see it being loaded and included in the initial table. Please see below, where you'll notice the Forecast column in column 29.

However, you'll see that after the Removed Other Columns1 step - column 29 has been removed. 

I cannot seem to find a way to prevent this step of the query from keeping column 29 and not removing it. The M code for this line is:

    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (10)"}),

Which doesn't seem to reference actual column names, so I'm wondering how it's actually being coded to determine which columns to keep? 

Thanks