Power Query M: filter rows by table

[webportal]

Is there a way to read the "Text1", "Text2", etc. from the command below from a table?

Table.SelectRows(DB, each [Name] = "Text1" or [Name] = "Text2" or [Name] = "TextN")

I'd like to import a table with all these names from an Excel file into Power BI Desktop and automatically load only the rows in this table.

[MFelix · Accepted solution]

Hi @webportal,

Please correct me if I'm wrong, you have an excel file with several tables with different names "Text1", "Text2", .... and you want to pickup only the table you refer in a specific research?

If this is the case I would suggest to create a function query and then create a table with the words you are looking for then run the function against that table.

Check below the function for the Table formatting, and the code for using the fuction based on a list of names:

Function code

(TableName) =>

let
    Source = Excel.Workbook(File.Contents("C:\Users\user\Desktop\Tables.xlsx"), null, true), // change to the path of your file
    Table1_Table = Source{[Item=TableName,Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Cat", type text}, {"Value", Int64.Type}, {"Type", type text}})
in
    #"Changed Type"




Using function to get data

let
    Source = {"Table1", "Table3"}, //Replace name of tables in this part of the code
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Text to Find"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Text to Find", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Table1", each Table_Format([Text to Find])),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Invoked Custom Function", "Table1", {"Cat", "Value", "Type"}, {"Table1.Cat", "Table1.Value", "Table1.Type"})
in
    #"Expanded Table1"

You can then do a lot of variations on this, namely to pick up the table names, the first part can be made to do a lot of more changes than just formatting columns, is just an example.

Regards,

MFelix