Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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, w...
  • ronrsnfld's avatar
    2 years ago

    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