Forum Discussion
How to merge two table with conditional statement based on values of both tablea
Hi gpiero
Try this one with Table 1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcivKTEms1FFwLE0vLS5RMNZRMDIwtFDSUVIwVFCK1YlWCk9NyUstBqsJTi0oSc1NSi1SMMVQFpJRWgRR5Z9ckg9SY4RFUWkqqhpjA1Q1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CreatedDate = _t, IDAnomaly = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CreatedDate", type date}, {"IDAnomaly", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"IDAnomaly"},Table2,{"IDAnomaly"},"Table2",JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each let mytime=[CreatedDate] in Table.SelectRows([Table2],each mytime>=[StartPeriod] and mytime<=[EndPeriod])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TimeContr1", "TimeContr2"}, {"TimeContr1", "TimeContr2"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Table2"}) in #"Removed Columns"thank you very much, your help is like a tresure.
It works,
Thanks again:smileyhappy:
Hi,
after several months I have been using your solution, I am facing a new case.
I tried several solution but nothing up to know
Here the problem
#"Added Conditional Column4" = Table.AddColumn(#"Merged Queries", "TableTime", each let mydate = [ActGoodsMovDate], myhu = [HU Level] in Table.SelectRows([DLV_CycleTime], each mydate >= [StartingPeriod] and mydate <= [EndingPeriod])) and myhu = 1,
#"Added Conditional Column4" = Table.AddColumn(#"Merged Queries", "TableTime", each let mydate = [ActGoodsMovDate], myhu = [HU Level] in Table.SelectRows([DLV_CycleTime], each mydate >= [StartingPeriod] and mydate <= [EndingPeriod])) and myhu = 1,
The code in bold is the new coding I am trying to add.
The follwing another test but no success.
#"Added Conditional Column4" = Table.AddColumn(#"Merged Queries", "TableTime", each let mydate = [ActGoodsMovDate] and myhu = [HU Level] in Table.SelectRows([DLV_CycleTime], each mydate >= [StartingPeriod] and mydate <= [EndingPeriod])) and myhu = 1,
Could you help me to find the solution?
Thank you in advance.
Could you be misplacing the brackets???
Also the variables in let expression need to be separated by comma
Try this one
#"Added Conditional Column4" = Table.AddColumn(#"Merged Queries", "TableTime", each let mydate = [ActGoodsMovDate], myhu = [HU Level] in Table.SelectRows([DLV_CycleTime], each mydate >= [StartingPeriod] and mydate <= [EndingPeriod] and myhu = 1 ))