Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
gpiero
Skilled Sharer
Skilled Sharer

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

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

 

Image1.PNG

 

 

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

 

 

Image2.PNG

 

 

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

 

 

If I can...
3 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

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"

Regards
Zubair

Please try my custom visuals

View solution in original post

@Zubair_Muhammad

 

I am confirming it works.

 

This is what I am looking for.

Many thanks again

If I can...

View solution in original post

@Zubair_Muhammad 

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

 

It works,

 

Thanks againSmiley Happy

If I can...

View solution in original post

9 REPLIES 9
Zubair_Muhammad
Community Champion
Community Champion

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"

Regards
Zubair

Please try my custom visuals

@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

 

pqe.png

 


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

thank you. I'll try to apply asap.

 

 

If I can...

@Zubair_Muhammad

 

I am confirming it works.

 

This is what I am looking for.

Many thanks again

If I can...

@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

If I can...

@gpiero

 

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


Regards
Zubair

Please try my custom visuals

@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.

 

If I can...

@Zubair_Muhammad 

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

 

It works,

 

Thanks againSmiley Happy

If I can...

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


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.