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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.