Power Query: Table Select Columns

M

masterswordz

New member
Joined
Apr 28, 2020
Messages
22
Reaction score
0
Points
0
Location
Philippines
Excel Version(s)
2016
Just a quick question is there a way to Table.SelectColumns for an excel table source based on the Column Number and not the Column Name?

I have a use case where my users sends their table but the table headers are not the same which ends up giving an "Expression.Error: The column 'Assign Date1' of the table wasn't found.". The data I need is always in the 3rd and 4th Column of the table, however since the user is not really familiar in doing things, they consider this 3rd column header as a free from text. So they put anything they want as a Column header name.
 
Demote the headers to the first row. Then you will have Column1, Column2, etc. You can then delete the first row if you don't need it. You can change the column names if you desire or keep them as Column 1, etc.


EDIT: Ken types faster :)
 
Here is one way, get a list of column names, and then using the column number as an index to get the name and select by that name

Code: 
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    data.Type = Table.TransformColumnTypes(Source,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"e", Int64.Type}}),
    col.Select = Table.SelectColumns(data.Type, {Table.ColumnNames(data.Type){1}})
in
    col.Select[/FONT]

Don't forget the list is 0 based.
 
You must log in or register to reply here.
Back
Top