Forum Discussion

gpiero's avatar
gpiero
Skilled Sharer
7 years ago
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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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's avatar
    gpiero
    7 years ago

    Zubair_Muhammad

     

    I am confirming it works.

     

    This is what I am looking for.

    Many thanks again

  • gpiero's avatar
    gpiero
    7 years ago

    Zubair_Muhammad 

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

     

    It works,

     

    Thanks again:smileyhappy:

gpiero's avatar
gpiero
Skilled Sharer
7 years ago

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's avatar
gpiero
Skilled Sharer
7 years ago

Zubair_Muhammad 

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

 

It works,

 

Thanks again:smileyhappy: