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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.LeanAndPractise(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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors