March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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, with condition - id_form.
Thank You in advance 🤜💢🤛
Solved! Go to Solution.
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
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
Tricky and concise solution, but with little restriction. (options of "number" can not contain "1" and "11" simultaneously; if so, CONTAINSSTRING() fails)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
55 | |
26 | |
17 | |
13 |