Results 1 to 6 of 6

Thread: Power Query: Table Select Columns

  1. #1
    Acolyte masterswordz's Avatar
    Join Date
    Apr 2020
    Location
    Philippines
    Posts
    22
    Articles
    0
    Excel Version
    2016

    Question Power Query: Table Select Columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,379
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by masterswordz
    ...is there a way to Table.SelectColumns for an excel table source based on the Column Number and not the Column Name?...
    No, but you can work around the issue using the approach found here: https://www.excelguru.ca/blog/2020/0...fferent-names/
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    237
    Articles
    0
    Excel Version
    2019
    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

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    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:
    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
    Don't forget the list is 0 based.

  5. #5
    Acolyte masterswordz's Avatar
    Join Date
    Apr 2020
    Location
    Philippines
    Posts
    22
    Articles
    0
    Excel Version
    2016
    Thanks this could be a workaround for me!

  6. #6
    Acolyte masterswordz's Avatar
    Join Date
    Apr 2020
    Location
    Philippines
    Posts
    22
    Articles
    0
    Excel Version
    2016
    This definitely worked for me! Thanks Ken!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •