Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Search for text, a set format, across two columns, in one table

  • 1.  Search for text, a set format, across two columns, in one table

    Posted Nov 11, 2019 10:20 AM
    Hi All

    Not sure if this is best worked using powerquery, but I am open to options or ideas.  Ideally I want to have this built into my PowerBI so it becomes an automated process or action in the background.

    So as normal I have created/setup a dataSource or connection to an Excel worksheet which is imported into PowerBI desktop.  Once the data has been imported I want  to perform a few checks on two key columns then save the result or outcome to a separate column.

    Here is my question or dilemma.  I have table in PowerBI with say 10 columns along with a good number of rows. Within this table there are two key columns, one is called "Notes" and the second is called "Projects", both columns are populated with a block of text of a various nature and size; various characters and spaces/para etc a jumble of text basically.   I want to search through each of these columns to try and identify if the following format/text/pattern of four letters "ABGG" are quoted in either column; so I need to check each column(x2) per row.  If I do find any reference to this patter 'ABGG', from either column or both, I then want to flagged this at the end of the table in new column; new column heading could be 'ABGG_FLAG', as a value 'YES'.  So in theory I will have the same numbers of rows I started with but a few will have the value 'YES', in the last or new column indicating, the text 'ABGG' appeared in one or both columns above.  Is this doable?

    Thanks Chris

    ------------------------------
    Chris


    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Search for text, a set format, across two columns, in one table

    Posted Nov 11, 2019 01:10 PM
    Hi Chris,  you could use a "CASE" statement.  Not sure what language you're using for the extract, but I use SQL for the data source, so would say:
        CASE when Notes like 'ABGG%' AND Projects like 'ABGG%' then 'Yes' ELSE 'No' END

    I believe there is a DAX equivalent, which would be SWITCH with a SEARCH function.  I have used that methodology in one of my extracts, but can't seem to locate it. Still think it's much simpler to utilize SQL functions for the extracts (especially with joins!) and DAX for basic functions.


    ------------------------------
    Nancy Wilson
    Sr. MIS Specialist/Analyst/Programmer
    Blanchester OH
    7407831658
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Search for text, a set format, across two columns, in one table

    Bronze Contributor
    Posted Nov 12, 2019 01:37 AM
    First Merge the columns you need to check.
    EG: [Notes]&"-"&[Projects]
    Then search for the phrase in the column:


    ------------------------------
    Mohamed Ibrahim
    ------------------------------

    Conference-PBI_200x200