Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
dariaglb
Frequent Visitor

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

 

dariaglb_0-1714288172002.png

 

table 2 has number in columne

dariaglb_1-1714288187649.png

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

 

dariaglb_2-1714288298386.png

 

Thank You in advance  🤜💢🤛

 

 

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

Process Table 1 to produce one row for each entry:

Original Table 1

ronrsnfld_0-1714300550604.png

 

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

ronrsnfld_1-1714300617026.png

 

  • 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

ronrsnfld_2-1714300928638.png

 

Final Result

ronrsnfld_3-1714300962115.png

 

 

 

 

 

View solution in original post

2 REPLIES 2
ThxAlot
Super User
Super User

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

 

ThxAlot_0-1714313131145.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



ronrsnfld
Super User
Super User

Process Table 1 to produce one row for each entry:

Original Table 1

ronrsnfld_0-1714300550604.png

 

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

ronrsnfld_1-1714300617026.png

 

  • 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

ronrsnfld_2-1714300928638.png

 

Final Result

ronrsnfld_3-1714300962115.png

 

 

 

 

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors