How to Convert each value in List form other table with conditions

[dariaglb]

Hi, please help  🙂

I have two tables in Power Query

one has numbers in rows

table 2 has number in columne

i need to replase each number in Table1 to decode from Table2, with condition - id_form.

Thank You in advance  🤜💢🤛

[ThxAlot]

Tricky and concise solution, but with little restriction. (options of "number" can not contain "1" and "11" simultaneously; if so, CONTAINSSTRING() fails)

[ronrsnfld · Accepted solution]

Process Table 1 to produce one row for each entry:

Original Table 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUUTDSUTBWitWJVjKC8sEcY7ikiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id_form = _t, zones = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id_form", Int64.Type}}),
    #"zones to list" = Table.TransformColumns(#"Changed Type", 
        {"zones", each List.Transform(Text.Split(_,","), each Number.From(_)), type {number}}),
    #"Expanded zones" = Table.ExpandListColumn(#"zones to list", "zones")
in
    #"Expanded zones"

Processed Table 1

• Join with Table 2 based on id_form, zone and number
• Group by zone
• id_form
• Concatenate the zones

let
    Source = Table.NestedJoin(#"Table 1", {"id_form", "zones"}, #"Table 2", {"id_form", "number"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(Source, "Table 2", {"decode"}, {"decode"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table 2", {"id_form"}, {
        {"zones", each Text.Combine([decode],", "), type text}})
in
    #"Grouped Rows"

Joined #"Expanded Table" step

Final Result