Forum Discussion

Centaur's avatar
Centaur
Helper V
4 years ago
Solved

adding Column to Source Data but doesnt show in Refresh

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
Need Help
Tips and Tricks
  • KNP's avatar
    KNP
    4 years ago

    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's avatar
Centaur
Helper V
4 years ago

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's avatar
KNP
Super User
4 years ago

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's avatar
    KNP
    Super User
    4 years ago

    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's avatar
      Centaur
      Helper V
      4 years ago

      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.