Forum Discussion
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 Column "Name" & Last 32 Columns.
Below is my query and I stumble at the last after LastNCol. LastNCol successfully lists the 32 columns I want but when I then want to combine these with "Name" I get an error
Much appreciated any help to point me in the right direction.
TY
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]),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Day #(lf)%chg", "Day RANK", "30days", "30day RANK", "10days", "10days RANK", "5days", "5days RANK", "3days", "3days RANK", "AVG", "% Diff to MAX", "MAX", "Days since LARGE", "DAYS", "TRUE", "Column1619", "10 days", "5 days", "30 days"}),
LastNCol = List.LastN(Table.ColumnNames(#"Removed Columns"),32),#"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Name"} & {LastNCol})
in
#"Removed Other Columns"
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"
4 Replies
1500 columns? What could possibly go wrong? Extra points if you know the column limit in Power BI.
What's the error message? Is [Name] always the first column?
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]), LastNCol = List.LastN(Table.ColumnNames(#"Promoted Headers"), 32), #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns", {"Name"} & {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"
