Forum Discussion
Solved
How to merge two table with conditional statement based on values of both tablea
Hi, I am trying to solve a problem in a Power BI report but it is likely bigger than my skills up to now. I have a list on Sharepoint like the pict below (I put only columns linked to my issue...
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 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"
Please see sample file attached as well
You can follow the steps from Query Editor
Bascially you merge the 2 tables using anomaly column
then
Use Table.SelectRows to select the relevant row from Table 2
Hi,
sorry but something is wrong.
I am facing a data duplication
#"Merged Queries2" = Table.NestedJoin(#"Added Conditional Column",{"AnomalyReasonId"},IDReasonCycleTime,{"AnomalyReasonID"},"IDReasonCycleTime",JoinKind.LeftOuter), #"Added Conditional Column1" = Table.AddColumn(#"Merged Queries2", "ContractorTime", each let mytime = [CreatedDate] in Table.SelectRows([IDReasonCycleTime],each mytime >=[StartPeriod] and mytime <= [EndPeriod])),Did I put in a wrong way your code?
Regards