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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EZimmet
Resolver I
Resolver I

Use Excel input for Text Contains function within PBIX

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]

 

pbix.JPG

1 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

MFelix_0-1695940083333.png

 

MFelix_1-1695940091401.png

 

 


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

4 REPLIES 4
EZimmet
Resolver I
Resolver I

Thank You Miguel

You were spot on with this solution 😎

MFelix
Super User
Super User

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.

 

 


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



@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      

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

MFelix_0-1695940083333.png

 

MFelix_1-1695940091401.png

 

 


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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors