none
Lookup type search problem RRS feed

  • Question

  • Hi,

    I have 2 tables :

    Table1

    Job Name, Step Name,  Command

    In the Command field I have text like 'EXEC job1' or  'use msdb EXEC job2'

    Note, there is no simple pattern match I can use as Commands are highly variable. 

    Table2 contains a column called Procedure, it holds things like Job1, Job2, Job3

    I would like to add a column to Table1 returning the Procedure from Table2 if the name of the procedure, Job1 etc.. is found in the command field.

    I don't think there there is a simple way to do this i.e. by splitting fields and joining.  Wondered if there is an M function that will do this.


    Lee Hawthorn. View my Performance Management blog at leehbi.com

    Wednesday, July 20, 2016 11:59 AM

Answers

  • Hi Lee,

    In your Table1 query, you can add a custom column with a formula like:

    Table.AddColumn(
        <PreviousStepName>, 
        "Procedure", 
        each List.Select(
                    Table2[Procedure], 
                    (current)=>Text.Contains(
                                          [Command], 
                                          current, 
                                          Comparer.OrdinalIgnoreCase
                                      )
                ){0}?

    )

    Wednesday, July 20, 2016 3:09 PM

All replies

  • I sorted this back in SQL by joining the tables with a LIKE predicate.

    Lee Hawthorn. View my Performance Management blog at leehbi.com.

    Wednesday, July 20, 2016 12:38 PM
  • Hi Lee,

    In your Table1 query, you can add a custom column with a formula like:

    Table.AddColumn(
        <PreviousStepName>, 
        "Procedure", 
        each List.Select(
                    Table2[Procedure], 
                    (current)=>Text.Contains(
                                          [Command], 
                                          current, 
                                          Comparer.OrdinalIgnoreCase
                                      )
                ){0}?

    )

    Wednesday, July 20, 2016 3:09 PM
  • Thank Colin - neat trick.

    Lee Hawthorn. View my Performance Management blog at leehbi.com. Previously CIMA/CGMA Management Accountant before moving into BI.

    Friday, July 22, 2016 10:10 AM