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"

5 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    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?

     

  • 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"