Conditional Merge of 2 queries / if then statement?

[Anonymous]

Hi All,

Hoping someone can help. I have 2 queries which I want to conditionally merge. Both queries have the columns COST CENTRE, SHIFT, WEEKDAY, etc). At this point it can be done by a straight merge.

The issue is that 1 query has the field "occupancy", and the other query has 2 fields "occupancy low" and "occupancy high".

What I want to do is merge all fields that have exact match columns (green), then find the "Occupancy Low" and "Occupancy High" line that matches "occupancy" in query 1 (blue columns). Finally I want to also pull in an additional column from Query 2 (orange). I have made a dummy of the data below that is colour coded as per above. Hoping someone can assist out there.

[Anonymous]

Thankyou resident rockstar. This worked a treat and the report as of today is now in production. Kudos to you.

[Vijay_A_Verma · Accepted solution]

This is the query for Table1. This refers to Table2. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRcvQFEgZAbGiqFKsTrQQWNwfyA+ASRmAJsLgFkO8XDBIEYqCGWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cost Centre" = _t, Shift = _t, Weekday = _t, Occupancy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost Centre", Int64.Type}, {"Shift", type text}, {"Weekday", Int64.Type}, {"Occupancy", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Cost Centre", "Shift", "Weekday"}, Table2, {"Cost Centre", "Shift", "Weekday"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Occupancy Low", "Occupancy High", "Required"}, {"Occupancy Low", "Occupancy High", "Required"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each [Occupancy]>=[Occupancy Low] and [Occupancy]<=[Occupancy High]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

 If you need the code for sample Table 2 as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRcvQFEgZAbGgCJIxALBOlWJ1oJbACcyA3AKYCpNzQGEgYgxWA5S2AXL9gkARUkSmYDZKHWxGAaYWhOaoSuCss4MqUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cost Centre" = _t, Shift = _t, Weekday = _t, #"Occupancy Low" = _t, #"Occupancy High" = _t, Required = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost Centre", Int64.Type}, {"Shift", type text}, {"Weekday", Int64.Type}, {"Occupancy Low", Int64.Type}, {"Occupancy High", Int64.Type}, {"Required", Int64.Type}})
in
    #"Changed Type"