Forum Discussion
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...
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"
Hi lbendlin
1500+ columns guessing it's exceedng the column limit in Power BI (EEEEK!) Is Power BI same as Power Query?
Yes, Name always the 1st column
Error is -
Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]
Thanks for looking into this for me!
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"