Forum Discussion
Helper IIIConditional Merge or Table.SelectRows
try this
cp = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2]))), tje = Table.ExpandTableColumn(cp, "join", {"Field1", "Field2", "Field3"}, {"join.Field1", "join.Field2", "join.Field3"}), fr = Table.SelectRows(tje, each ([join.Field3] <> null)) in fr
Helper IIIMany thanks for the suggestion mahoneypat
Your approach includes a number of steps at the end which I had considered but was hoping to avoid. I was hoping there might be some more elegant syntax around a 'conditional join' where the join carried out the the OR test. Either that or including the conditional logic of Table.SelectRows.
Do you think either of those approaches is possible in any way?
try this
cp = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2]))),
tje = Table.ExpandTableColumn(cp, "join", {"Field1", "Field2", "Field3"}, {"join.Field1", "join.Field2", "join.Field3"}),
fr = Table.SelectRows(tje, each ([join.Field3] <> null))
in
fr
- ghdunn
I like this Anonymous as it seems to be what i had in mind...
But...trying to incorporate your code in mine....just adding the first row
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUapQitWJVkoCsirBrGQgqwrMSgGyypViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Field1 = _t, Field2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Field1", type text}, {"Field2", type text}}),
#"Added Custom" = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2])))
in
#"Added Custom"I get:
Expression.Error: A cyclic reference was encountered during evaluation.
If this code is, as I suppose, inside query which load tableA:
you should change my tabA with the variable of previuos step namely #"Changed Type".
Also you should change tabB with the variable referencing tableB value, wich may be the name of the query where table B is loaded in.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUapQitWJVkoCsirBrGQgqwrMSgGyypViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Field1 = _t, Field2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Field1", type text}, {"Field2", type text}}),
#"Added Custom" = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2])))
in
#"Added Custom"PS
how and where is the code related to table B?
- ghdunn
Many thanks Anonymous and mahoneypat
Just into my week of work now...I will test both suggestions over the next 48 hoursa days and comment
Thanks guys!
ged