Use Excel input for Text Contains function within PBIX

[EZimmet]

Good Day and Thank you 

I'm looking to see how to perform the below sorry I can't provide more info as I've never attempted this prior not really sure how to phrase it.

• Currently have a SQL DB with all my source Data loaded into the PBIX
• Also have a SharePoint linked to the PBIX with an excel file that I would like to use as input for a  Text.Contains formula. Since I don’t want to edit a Power Query each month to hard code in  200+ lines if someone can assist in how to use the Excel as input to a Text.Contains formula
• The Key field is [Path]

[EZimmet]

Thank You Miguel

You were spot on with this solution 😎

[MFelix]

Hi @EZimmet ,

Not really sure if I understand your requirements. What do you mean to use the excel file in a Text.Contains Formula do you want to have a filter on the tables that uses the values from the Excel file?

You have several lines in the table but you only are highlithing one, do you only want the first result?

Can you please provide more insights.

[EZimmet]

@MFelix 

TY for your response

This is being used for doing Data Center Rack Audits / The Text.Contains is the Parent Rack in the Excel

The PBX has all the Data Center inventory – The Path field shows the Rack then the devices in the rack {Ex. Server, Switch...}

We would like to use the Excel file as our input for what would be inserted into the Text.Contains formula to automate the process otherwise we would need to update the Custom Column every few months insert with the new audits and republish

In the example I only bolded the first line so it would stand out but yes every record in the Excel will have associated records found in the PBIX

Hope this helps and TY Again for assisting      

[MFelix · Accepted solution]

Hi @EZimmet ,

Add a new colum with the following code:

if
  List.AnyTrue(
    List.Transform(
      Table1_Sharepoint[Path],
      (x) => Text.Contains([Path], x)
    )
  ) then "Text Exists" else null