Forum Discussion
Helper IVTables merge with conditional
Hi guys,
I need to merge two tables:
First table - Car
____________________________________________________________
| Car | TireSize | Dimension | Speed |
---------------------------------------------------------------
| Brand_1 | 16 | 46 | 4 |
| Brand_2 | 14 | 45 | 2 |
| Brand_3 | 16 | 46 | 5 |
---------------------------------------------------------------
Second Table - Tire
____________________________________________________________
| Tire | TireSize | Dimension | Speed |
---------------------------------------------------------------
| Tire_1 | 16 | 46 | 5 |
| Tire_2 | 14 | 45 | 2 |
---------------------------------------------------------------
I need to merge both columns, knowing:
Car.TireSize = Tire.TireSize
Car.Dimension = Tire.Dimension
Car.Speed <= Tire.Speed
My problem is with the <= 🙂
Any help out there?
Thanks!
Its easy to do it in Transform data/ Query editor.
1. Merge Car and Tire(Left outer - all rows from Car) based on (Tiresize and Dimension)
2. Expand the table column(Tire).
3. Create a new conditional column - If Speed column from Tire is >= speed column from Car then '1' else '0'.
4. select value 1 from the column created from step 3.
M query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUnAqSsxLiTc8tEABjJR0lIC0oRmMj4kgKkzwqEBRh1VWKVYHbrURmtXYdSAbaUqc1UYErTamla+xu1ApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#" Car " = _t, #" TireSize " = _t, #" Dimension " = _t, #" Speed " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{" Car ", type text}, {" TireSize ", Int64.Type}, {" Dimension ", Int64.Type}, {" Speed ", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {" TireSize ", " Dimension "}, Tire, {" TireSize ", " Dimension "}, "Tire", JoinKind.LeftOuter),
#"Expanded Tire" = Table.ExpandTableColumn(#"Merged Queries", "Tire", {" Tire ", " TireSize ", " Dimension ", " Speed "}, {"Tire. Tire ", "Tire. TireSize ", "Tire. Dimension ", "Tire. Speed "}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Tire", "Flag", each if [#"Tire. Speed "] >= [#" Speed "] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Flag] = 1))
in
#"Filtered Rows"You could even edit the M query to remove the coditional column and the filtered rows as below.
#"Filtered Rows" = Table.SelectRows(#"Expanded Tire", each ([#"Tire. Speed "] >= [#" Speed "]))
If this helps, mark it as a solution.
Kudos are nice too.
3 Replies
- VasTg
Memorable Member
Its easy to do it in Transform data/ Query editor.
1. Merge Car and Tire(Left outer - all rows from Car) based on (Tiresize and Dimension)
2. Expand the table column(Tire).
3. Create a new conditional column - If Speed column from Tire is >= speed column from Car then '1' else '0'.
4. select value 1 from the column created from step 3.
M query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUnAqSsxLiTc8tEABjJR0lIC0oRmMj4kgKkzwqEBRh1VWKVYHbrURmtXYdSAbaUqc1UYErTamla+xu1ApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#" Car " = _t, #" TireSize " = _t, #" Dimension " = _t, #" Speed " = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{" Car ", type text}, {" TireSize ", Int64.Type}, {" Dimension ", Int64.Type}, {" Speed ", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {" TireSize ", " Dimension "}, Tire, {" TireSize ", " Dimension "}, "Tire", JoinKind.LeftOuter),
#"Expanded Tire" = Table.ExpandTableColumn(#"Merged Queries", "Tire", {" Tire ", " TireSize ", " Dimension ", " Speed "}, {"Tire. Tire ", "Tire. TireSize ", "Tire. Dimension ", "Tire. Speed "}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Tire", "Flag", each if [#"Tire. Speed "] >= [#" Speed "] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Flag] = 1))
in
#"Filtered Rows"You could even edit the M query to remove the coditional column and the filtered rows as below.
#"Filtered Rows" = Table.SelectRows(#"Expanded Tire", each ([#"Tire. Speed "] >= [#" Speed "]))
If this helps, mark it as a solution.
Kudos are nice too.
