Forum Discussion
Anonymous
2 years agoNot applicable
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...
- 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
ronrsnfld
2 years agoSuper User
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