Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |