Forum Discussion

EZimmet's avatar
EZimmet
Resolver I
2 years ago
Solved

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]

 

Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    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

     

     

     

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    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's avatar
      EZimmet
      Resolver I
      2 years ago

      @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's avatar
        MFelix
        Super User
        2 years ago

        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

         

         

         

  • EZimmet's avatar
    EZimmet
    Resolver I
    2 years ago

    Thank You Miguel

    You were spot on with this solution 😎