How to merge two table with conditional statement based on values of both tablea

[gpiero]

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 issues).

The yellow fields would be the solution I am looking for. 

The 2nd table below contains the time spent by each Contractor according to IDAnomaly Type.

Now I need to put in the table 1 each right value of the 2nd table when CreatedDate of each ID is >= StartPeriod and <=EndPeriod.

I have tried adding conditional column in Edit Query mode, but I was not able to find the right solution.

Thanks in advance for any suggestion.

Regards

[Zubair_Muhammad · Accepted solution]

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"

[gpiero · Accepted solution]

@Zubair_Muhammad

I am confirming it works.

This is what I am looking for.

Many thanks again

[Zubair_Muhammad · Accepted solution]

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"

[Zubair_Muhammad]

@gpiero

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

[gpiero]

@Zubair_Muhammad

thank you. I'll try to apply asap.

[gpiero · Accepted solution]

@Zubair_Muhammad

I am confirming it works.

This is what I am looking for.

Many thanks again

[gpiero]

@Zubair_Muhammad

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

[Zubair_Muhammad]

@gpiero

Could you share your file or sample data for me to check?

[gpiero]

@Zubair_Muhammad 

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.

[gpiero · Accepted solution]

@Zubair_Muhammad 

thank you very much, your help  is like a tresure.

It works,

Thanks again

[Zubair_Muhammad]

@gpiero 

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 ))