Tables merge with conditional

[efilipe]

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!

[VasTg · Accepted solution]

@efilipe 

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.

[efilipe]

This is actually a big tabled. There are a lot of Speeds. About 10 of them.

[efilipe]

Makes sense! I'll try! Thanks!