locked
Can I reference columns using relative references in Power Query? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a use case where I want to remove the last 4 columns in Power Query.  Each month I get a new file with a new column, and I want to unpivot the data.  But I would like to remove the last 4 columns (which are totals) before I unpivot.  I could just unpivot the lot and then filter them out (maybe), but I would like to know if I can reference the columns other than by name.

    I know I can count the columns with table.columncount but when I try to use table.removedcolumns, the syntax seems to only accept the name of the column.

    Thursday, March 13, 2014 11:56 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    You might use LastN:

      RemovedColumns=Table.RemoveColumns(FilteredRows,List.LastN(Table.ColumnNames(FilteredRows),4)),

    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Friday, March 14, 2014 4:38 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Well I actually have solved my own problem by returning the names of columns into a list, and then removing the last column.  I then iterate this 4 times for the 4 columns.

        RemovedColumns=Table.RemoveColumns(FilteredRows,List.Last(Table.ColumnNames(FilteredRows))),

    But I wonder if there is a more elegant solution anyone can suggest.

    Friday, March 14, 2014 12:24 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    You might use LastN:

      RemovedColumns=Table.RemoveColumns(FilteredRows,List.LastN(Table.ColumnNames(FilteredRows),4)),

    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Friday, March 14, 2014 4:38 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Marco's solution gets my vote as the most straightforward approach.
    Friday, March 14, 2014 1:54 PM