How to Find if Text is in List of Text?

[KamKam]

Hello,

I have a table that contains a single column (TableA). It looks something like this:

Scenario

A2019

A2020

A2021

If one of the values in TableA is within another column in TableB, I want only the values in TableA returned.

I.e. If this is Table B:

Col1

TotalSELL OUT (RRP incl. GST)TotalA2021Value

A2020Value

Dept StoreSELL OUT (RRP incl. GST)TotalA2021Value

It should become:

Col1

A2021

A2020

A2021

I did try creating a function that I intended to call when adding a new column:

(Loop as number, Value as text) =>
let
    Count = Table.RowCount(#"TableA") - 1,
    CurrentLoop = Loop + 1,
    Scenario = Lines.ToText(#"TableA"[Scenario]{CurrentLoop - 1}),
    Output = 
    if Text.Contains(Value, Scenario) or CurrentLoop >= Count
    then Text.ToList(Scenario)
    else @Query1(CurrentLoop, Value)
in
    Output

 When I run this however I get the following error:

Expression.Error: We cannot convert the value "A2019" to type List.

I get this error regardless of whether I include Text.ToList and Lines.ToText.

Please help

[AlexisOlson]

You can list all of the matching rows of TableA in a new custom column like this:

= Table.AddColumn(Source, "Custom",
    (row) => Text.Combine(
                 List.Select(
                     TableA[Scenario],
                     each Text.Contains(row[Col1], _)
                 ),
                 ","
             ),
     type text)

Full sample query:

let
    TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQyMLRUitUBs4wM4CxDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Scenario = _t]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScwJdvXxUfAPDVHQCAoKUMjMS87RU3APDtEESzoaGRgZhiXmlKYqxepEK4G4BgiuS2pBiUJwSX5RKtGGxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", (row) => Text.Combine(List.Select(TableA[Scenario], each Text.Contains(row[Col1], _)), ","), type text)
in
    #"Added Custom"

[CNENFRNL]

Why bother to use recursion ...

let
    TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQyMLRUitUBs4wM4CxDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Scenario = _t]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScwJdvXxUfAPDVHQCAoKUMjMS87RU3APDtEESzoaGRgZhiXmlKYqxepEK4G4BgiuS2pBiUJwSX5RKtGGxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),

    Lookup = let lookup = TableA[Scenario] in Table.TransformColumns(Source, {"Col1", each List.Accumulate(lookup, _, (s,c) => if Text.Contains(s,c) then c else s)})
in
    Lookup

[Greg_Deckler]

@KamKam Why not just:

(Loop as number, Value as text) =>
let
    Count = Table.RowCount(#"TableA") - 1,
    CurrentLoop = Loop + 1,
    Scenario = Lines.ToText(#"TableA"[Scenario]{CurrentLoop - 1}),
    Output = 
    if Text.Contains(Value, Scenario) or CurrentLoop >= Count
    then Scenario
    else @Query1(CurrentLoop, Value)
in
    Output

[KamKam]

Thank you for the suggestion @Greg_Deckler.

I have tried that and get the same error. Can you think of anything else that may work?

[Greg_Deckler]

@KamKam I made some edits, this worked for me (below). You call this in a add column formula like so: #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Query1(-1,[Col1]))

(Loop as number, Value as text) as text =>
let
    __Table = Table.SelectColumns(#"TableA", "Scenario"),
    __Count = if Loop = -1 then Table.RowCount(__Table) else Loop,
    __Text = __Table[Scenario]{__Count - 1},
    __Output = if Text.Contains(Value, __Text) or __Count - 1 = 0 then __Text else @Query1(__Count - 1, Value)
in
    __Output

[KamKam]

Thanks @Greg_Deckler,

While your method did not throw any errors it is only returning the first value in TableA ("A2019"). Are you sure that we are still looping through TableA's values this way?

[Greg_Deckler · Accepted solution]

@KamKam Seems to work for me. Easier to just share the PBIX. Attached below sig.