cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
webportal Member
Member

Power Query M: filter rows by table

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Power Query M: filter rows by table

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

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
Super User
Super User

Re: Power Query M: filter rows by table

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

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!