How to combine two columns and add a time offset

[GoldBarz]

Hi, I have two recovery % values in two seperate columns associated with a single date (without a time stamp) in my query editor. I would like to seperate shift 1 and shift 2 such that shift 2 is offset 12 hours from shift 1 and they can be compared all in a single coumn. Does anyone know how I could go about this? Thank you very much, your help is most appreciated.

[parry2k · Accepted solution]

@GoldBarz start a new query, click advanced editor and paste this code, you can tweak it as you see fit

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZNJjgMxCEWvEpVUu4gwGLD7KlHuf42u8hiTnRdPwH/g9/ugF70YmR/4h3g8DxfQdN4PAvLz+DxvhjdGFUQqk8F0MLIxGUH8ZjID22DSzjAUrQyByWB0Z655Wh0B58HYzjhQroxBnvN4rEO1jjukMpi8MZZB6jymoDiYsvtJPZcjlOmHcIcIHM9m02cwCqavbjjST0PEURFzh8qMTxKhTB2S1W6X7VYz1QdPAaQxXWqQ1jPokMUTsaYgAdOEPLYz6u1sVdqFX7sg64dU1ky78TtUU4Cgsx3jz3pleFoQxZvMoxLmCXGcqSsoUBYkcXeW++C+oGC81N9RHzrXwhqhkvrtfkEWFVA7lcv4DX3+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Met Bal % Au Rec - Shift 1" = _t, #"Met Bal % Au Rec - Shift 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Met Bal % Au Rec - Shift 1", Percentage.Type}, {"Met Bal % Au Rec - Shift 2", Percentage.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Met Bal % Au Rec - Shift 1", "1"}, {"Met Bal % Au Rec - Shift 2", "2"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Date"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "DateTime", each [Date]+(#duration(0,if [Attribute] = 1 then 6 else 12,0,0)), type datetime),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"DateTime", "Value"})
in
    #"Removed Other Columns"

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@GoldBarz Got it, can you also paste the sample data into the table format instead of an image.

[GoldBarz]

Date	Met Bal % Au Rec - Shift 1	Met Bal % Au Rec - Shift 2
1/1/2022 0:00	73.54%	71.17%
1/2/2022 0:00	55.33%	78.65%
1/3/2022 0:00	80.37%	82.26%
1/4/2022 0:00	82.95%	81.63%
1/5/2022 0:00	83.57%	83.72%
1/6/2022 0:00	87.18%	86.85%
1/7/2022 0:00	83.17%	77.49%
1/8/2022 0:00	68.35%	65.50%
1/9/2022 0:00	74.37%	70.97%
1/10/2022 0:00	71.70%	73.73%
1/11/2022 0:00	78.30%	81.66%
1/12/2022 0:00	82.22%	81.92%
1/13/2022 0:00	82.81%	81.33%
1/14/2022 0:00	76.50%	76.29%
1/15/2022 0:00	74.40%	75.54%
1/16/2022 0:00	73.67%	74.21%
1/17/2022 0:00	76.61%	76.64%
1/18/2022 0:00	77.16%	78.99%
1/19/2022 0:00	81.32%	80.51%
1/20/2022 0:00	83.13%	81.91%
1/21/2022 0:00	80.82%	80.08%
1/22/2022 0:00	77.40%	79.98%
1/23/2022 0:00	78.68%	78.78%
1/24/2022 0:00	79.65%	79.53%
1/25/2022 0:00	79.94%	86.53%
1/26/2022 0:00	81.10%	84.23%

[GoldBarz]

Expected output would look like this:

Date	Recovery
1/1/2022 6:00	73.54
1/1/2022 18:00	71.17
1/2/2022 6:00	55.33
1/2/2022 18:00	78.65
1/3/2022 6:00	80.37
1/3/2022 18:00	82.26
1/4/2022 6:00	82.95
1/4/2022 18:00	81.63
1/5/2022 6:00	83.57
1/5/2022 18:00	83.72
1/6/2022 6:00	87.18
1/6/2022 18:00	86.85

[parry2k]

@GoldBarz not super clear, can you provide the expected output.