Replace “yes” found in one list with the actual string from another list

0 votes

I’m comparing two lists, ListA and ListB, to check if ListB’s row content appears in ListA’s row content at all. I can’t do a one-for-one match of both rows’ contents because the content of a row in ListB might only be one part of the content of a row in ListA.

PS.: In the query below, ListB includes “roo”, which is the first three letters in the word room. I would want to know that “roo” is in ListA’s row that has “in my room.”

What I want is to replace “yes” with the actual value from ListB — the value “roo,” for instance. I tried to simply substitute wordB for “yes” but wordB wasn’t recognized and an error was thrown at me.

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"roo", "me", "only"},
    contains_word=List.Transform(ListA, (lineA)=>if List.MatchesAny(ListB, (wordB)=>Text.Contains(lineA, wordB)) = true then "yes" else "no")
in
    contains_word

The current query results in the following :

    List
1   yes
2   yes
3   no
4   yes

I want the query results to be as the following :

    List
1   roo
2   me
3   
4   only

Any idea of how to do this?

Feb 19 in Power BI by Shubham
• 12,270 points
32 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

Objective : The idea is to use List.Transform twice, so that; 

  • Inner Transform : this list B is changed to leave only matching values
  • Outer Transform : the string from list A is replaced by the first non-null entity.

Code :

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"roo", "me", "only"},
    contains_word=List.Transform(ListA, (lineA)=>List.Select(List.Transform(ListB, (wordB)=>if Text.Contains(lineA, wordB) = true then wordB else null), (x)=>x <> null){0}?)
in
    contains_word

answered Feb 19 by Upasana
• 7,680 points

Related Questions In Power BI

0 votes
1 answer

Calculated column with the sum of values from many columns in a row

Hi, You can create a new column by ...READ MORE

answered Mar 14 in Power BI by CHERUKURI
• 13,940 points
12 views
0 votes
1 answer

list reports with calculated percentage in Power BI using dax

Create a Measure called Total Revenue: Total Revenue = SUMX( ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 14,080 points
53 views
+1 vote
1 answer

Show the latest value in PowerBi from ASA

Best you can do right now is ...READ MORE

answered Oct 25, 2018 in Power BI by Surendra
202 views
0 votes
1 answer

How to replace null values with custom values in Power BI(power query editor)?

Hi Nithin, To fill or replace the null or ...READ MORE

answered Mar 8 in Power BI by CHERUKURI
• 13,940 points
335 views
0 votes
1 answer

Power Query: Adding a Special Case

Try adding a Group By with Minimum ...READ MORE

answered Dec 27, 2018 in Power BI by Upasana
• 7,680 points
20 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14 in Power BI by Upasana
• 7,680 points
12 views
0 votes
1 answer

Cumulative row data over last 12 months in Power Query

Basically, what you do is,  Add an Index,  Group ...READ MORE

answered Apr 1 in Power BI by Upasana
• 7,680 points
41 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
135 views
0 votes
1 answer

How can i see my plugin name in the Power BI Visual plugin List ?

there is a couple of errors in your ...READ MORE

answered Nov 8, 2018 in Power BI by Upasana
• 7,680 points
31 views
0 votes
1 answer

Bar chart : Arranging in descending order using data from another chart

Select treemap.  Make it active.  Turn on Visual Interactions ...READ MORE

answered Feb 7 in Power BI by Upasana
• 7,680 points
13 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.