adding Column to Source Data but doesnt show in Refresh

[Centaur]

Hello I added a column to the source table in excel and thought that column would show after I refresh power query but I dont see it  How to show it in power query? 

thank you

[Centaur]

Hello, thanks for the reply.  I am a novice PQ user. 

Here is the Advanced Editor code:

I dont see that the column I added [Status] as being in the code right after Bank Name.  I am not sure if it will have to be manually added but would think that it would be added automatically. 

thank you

let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "tb")),
#"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Company", "Co Code", "Bank Name", "Account Name", "Date Closed", "Bank Key (ABA) Wire", "Bank Key (ABA) ACH", "Account No", "Origin ID", "CO ID", "Description", "SWIFT # (Domestic)", "SWIFT # (Foreign)", "House Bank", "Account ID (for SAP)", "CO ID2", "Vendor Code", "Opening date", "Wire", "ACH", "Check", "Positive Pay", "Collections", "Bank Relationship Manager", "Relationship Manager#(lf)Phone #", "Relationship Manager#(lf)Email", "Relationship Manager#(lf)Address", "Signatories", "Comments"}, {"Company", "Co Code", "Bank Name", "Account Name", "Date Closed", "Bank Key (ABA) Wire", "Bank Key (ABA) ACH", "Account No", "Origin ID", "CO ID", "Description", "SWIFT # (Domestic)", "SWIFT # (Foreign)", "House Bank", "Account ID (for SAP)", "CO ID2", "Vendor Code", "Opening date", "Wire", "ACH", "Check", "Positive Pay", "Collections", "Bank Relationship Manager", "Relationship Manager#(lf)Phone #", "Relationship Manager#(lf)Email", "Relationship Manager#(lf)Address", "Signatories", "Comments"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Content",{{"Name", "Table Name"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Bank Name", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Date Closed", type date}, {"Signatories", type text}, {"Comments", type text}, {"Bank Relationship Manager", type text}})
in
#"Changed Type"

[KNP]

I think I see the problem, by default, Power Query, when doing actions like expanding columns, explicitly names them. It's not ideal when things change, which is common.

Try this code, (I think I've typed this correctly but let me know)

let
  Source = Excel.CurrentWorkbook(),
  #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "tb")),
  #"Expanded Content" = Table.ExpandTableColumn(
    #"Filtered Rows",
    "Content",
    Table.ColumnNames(Table.Combine(#"Filtered Rows"[Content])),
    Table.ColumnNames(Table.Combine(#"Filtered Rows"[Content]))
  ),
  #"Renamed Columns" = Table.RenameColumns(#"Expanded Content", {{"Name", "Table Name"}}),
  #"Sorted Rows" = Table.Sort(#"Renamed Columns", {{"Bank Name", Order.Ascending}}),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Sorted Rows",
    {
      {"Date Closed", type date},
      {"Signatories", type text},
      {"Comments", type text},
      {"Bank Relationship Manager", type text}
    }
  )
in
  #"Changed Type"

The "Table.ColumnNames(Table.Combine(#"Filtered Rows"[Content]))" part takes care of getting all column names dynamically.

[KNP · Accepted solution]

Minor correction:

You don't need the second instance of the column names as that is only used if you want to change the column names. So this works...

let
  Source = Excel.CurrentWorkbook(),
  #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "tb")),
  #"Expanded Content" = Table.ExpandTableColumn(
    #"Filtered Rows",
    "Content",
    Table.ColumnNames(Table.Combine(#"Filtered Rows"[Content]))
  ),
  #"Renamed Columns" = Table.RenameColumns(#"Expanded Content", {{"Name", "Table Name"}}),
  #"Sorted Rows" = Table.Sort(#"Renamed Columns", {{"Bank Name", Order.Ascending}}),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Sorted Rows",
    {
      {"Date Closed", type date},
      {"Signatories", type text},
      {"Comments", type text},
      {"Bank Relationship Manager", type text}
    }
  )
in
  #"Changed Type"

[Centaur]

Hi, thank you very much.  It works!  I am not sure what I did to make the query exclude the column.  I am a novice user.  thanks again.  

[KNP]

It should show.

You've not really given enough information to get any help.

Can you share your Power Query code from the advanced editor?

Sample data if possible?