none
filtering multiple sheets by their contents in power query RRS feed

  • Question

  • Hi all, I have multiple excel files with multiple sheets in each of them. tables in sheets are in 3 different looks (in terms of the number of columns and row headers). I want to append (consolidate) all tables in one. unfortunately I cannot filter sheets by their names. I don't know any sheet's format and table structure unless I open it. so I cannot use "invoke custom function" to append them. does anybody know how i can filter sheets by any cell value? thanks,
    Sunday, January 28, 2018 2:43 PM

Answers

  • Try this within your custom column formula. It should give you a list of all column names on each sheet/table.

    each Table.ColumnNames([Data])

    If you're pulling from Tables, the tables nested within [Data] should have the correct column names.

    If you're pulling from Sheets, the column names will be on a particular row. You'll need to do an additional operation to get them. Something like:

    each Table.ColumnNames(Table.PromoteHeaders([Data]))

    or

    each Record.FieldValues([Data]{0})

    Where {0} indicates the row index. To get the 2nd row, use {1}. To get the third row, use {2}. And so on.

    Once you have the column names in a list, you can check the list for particular values. For example:

    each List.Contains(Record.FieldValues([Data]{0}), "SomeColumnName")

    Ehren


    Wednesday, January 31, 2018 5:00 PM
    Owner

All replies

  • Do the different kinds of sheets each have unique column name(s)? If so, you could use Table.ColumnNames to determine which kind of sheet it is.

    Ehren

    Monday, January 29, 2018 8:48 PM
    Owner
  • Thanks Ehren.  The thing is some time ago I issued some tables as templates that have been being used by several users.  Now I modified the template by adding and deleting some columns of original template.  Unfortunately some users keep using old template occasionally.  I cannot distinguish files by their names or sheet names,  so I am unable to direct them to proper functions.
    Tuesday, January 30, 2018 2:38 PM
  • Ok. Since it sounds like they have some column names in common, and some columns that are different, you should be able to use Table.Column names to distinguish what version of the template you're processing.

    Ehren

    Tuesday, January 30, 2018 5:39 PM
    Owner
  • "Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.ColumnNames([Data]){0})" gives back only "Column1" for all sheets. I think my sheets lack column names.
    Wednesday, January 31, 2018 6:22 AM
  • Try this within your custom column formula. It should give you a list of all column names on each sheet/table.

    each Table.ColumnNames([Data])

    If you're pulling from Tables, the tables nested within [Data] should have the correct column names.

    If you're pulling from Sheets, the column names will be on a particular row. You'll need to do an additional operation to get them. Something like:

    each Table.ColumnNames(Table.PromoteHeaders([Data]))

    or

    each Record.FieldValues([Data]{0})

    Where {0} indicates the row index. To get the 2nd row, use {1}. To get the third row, use {2}. And so on.

    Once you have the column names in a list, you can check the list for particular values. For example:

    each List.Contains(Record.FieldValues([Data]{0}), "SomeColumnName")

    Ehren


    Wednesday, January 31, 2018 5:00 PM
    Owner
  • Excccellent !!!!

    thanks Ehren. This one works wonderfully.

    Monday, February 5, 2018 7:08 AM