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