Forum Discussion

Moomins's avatar
Moomins
Frequent Visitor
2 years ago
Solved

Query to combine 1st Column & Last 32 Columns

Hi   I have a table with over 1500 columns.  The 1st column "Name" and the remaining columns are dates going back to 2017, dates are added to everyday. So I'd like a Query that will combine 1st C...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    the limit is actually 16000

     

    let
  Source = Excel.Workbook(File.Contents("C:\Users\PC\Desktop\HL Fund Prices1.xlsx"), null, true), 
  Prices_Sheet = Source{[Item = "Prices", Kind = "Sheet"]}[Data], 
  #"Removed Top Rows" = Table.Skip(Prices_Sheet, 2), 
  #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars = true]), 
  FirstNCol = List.FirstN(Table.ColumnNames(#"Promoted Headers"), 1), 
  LastNCol = List.LastN(Table.ColumnNames(#"Promoted Headers"), 32), 
  #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns", FirstNCol & LastNCol)
in
  #"Removed Other Columns"
lbendlin's avatar
lbendlin
Super User
2 years ago

the limit is actually 16000

 

let
  Source = Excel.Workbook(File.Contents("C:\Users\PC\Desktop\HL Fund Prices1.xlsx"), null, true), 
  Prices_Sheet = Source{[Item = "Prices", Kind = "Sheet"]}[Data], 
  #"Removed Top Rows" = Table.Skip(Prices_Sheet, 2), 
  #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars = true]), 
  FirstNCol = List.FirstN(Table.ColumnNames(#"Promoted Headers"), 1), 
  LastNCol = List.LastN(Table.ColumnNames(#"Promoted Headers"), 32), 
  #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns", FirstNCol & LastNCol)
in
  #"Removed Other Columns"
Moomins's avatar
Moomins
Frequent Visitor
2 years ago

Thank you so much lbendlin !! I appreciated your help.