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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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.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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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