How to Exclude a Column(s) in a Structured Reference to Table[#Data] (or similar)-CodePudding

I want to reference all the data in my dynamic table, except for the first two columns. My goal is to return the header of the first column that isn't blank, starting with the third column. I have the formula figured out for everything except the starting with the third column part. Is there an easy way to accomplish this? I'm thinking I might have to just do something like

`=Table[#Data] unless in the range of the first two columns'

Hoping for an easier way though.

EDIT: if my request isn't clear enough, I am looking for a formula that would produce the following exact situation in these circumstances. It must work in a table that can change size without issue, it must ignore the first two columns, it must scan a complete column of data from left to right before moving onto the next column (most of the formulas I've tried would give the result Aug-21 here), and it must return the header in basically any format.

CodePudding user response：

As you don't provide any sample data, I can only guess. So how about this formula

=INDEX($A$1:$D$1,MATCH(TRUE,$A$1:$D$1<>"",0))

CodePudding user response：

I don't have the time to write up a full answer for this, but you should use the "From Table" button "Get & Transform" section of the data ribbon.

Then, in the query editor window, In the home ribbon, click Manage Reference.To find the position of the first non-blank column will be hard, requiring learning Power-Query language, probably something like clicking the advanced editor and adding steps like

let
  Source = #"YourSourceQueryName",
  ColumnNames = Table.ColumnNames(Source),
  ColumnsToRemove = 2   List.PositionOf( // PositionOf is zero-based, returning -1 if all are blank
    List.Transform(
      List.RemoveFirstN( // list of column names except the first two
        ColumnNames,
        2
      ),
      (columnName) => List.IsEmpty(List.RemoveNulls(Table.Column(myTable, columnName)))
    ),
    false
  ), // Power query is lazy, so this won't actually look at every column, it will stop when it finds the first column!
  ColumnNamesToKeep = List.RemoveFirstN(
    ColumnNames,
    ColumnsToRemove
  ),
  ReturnTable = if (ColumnsToRemove = 1) then
    "All columns were blank!" // PositionOf returned -1!
  else
    Table.SelectColumns(Source, ColumnsToKeep)
in
  ReturnTable

You can now use this in other queries or you can load it to your spreadsheet. Unfortunately power query doesn't refresh live, you have to either explicitly refresh the query or use the "Refresh All" button in the data ribbon.

(I stressed the word "like" because I didn't debug. May contain syntax errors or other issues for you to debug.)