Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
webportal
Impactful Individual
Impactful Individual

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
MFelix
Super User
Super User

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

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.