Forum Discussion

GoldBarz's avatar
GoldBarz
New Member
4 years ago
Solved

How to combine two columns and add a time offset

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.

 

Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    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.

     

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    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.

     

  • GoldBarz's avatar
    GoldBarz
    New Member
    4 years ago

    Expected output would look like this:

     

     

    Date

    		Recovery
    1/1/2022 6:0073.54
    1/1/2022 18:0071.17
    1/2/2022 6:0055.33
    1/2/2022 18:0078.65
    1/3/2022 6:0080.37
    1/3/2022 18:0082.26
    1/4/2022 6:0082.95
    1/4/2022 18:0081.63
    1/5/2022 6:0083.57
    1/5/2022 18:0083.72
    1/6/2022 6:0087.18
    1/6/2022 18:0086.85

     

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

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

    • GoldBarz's avatar
      GoldBarz
      New Member
      4 years ago
      DateMet Bal % Au Rec - Shift 1Met Bal % Au Rec - Shift 2
      1/1/2022 0:0073.54%71.17%
      1/2/2022 0:0055.33%78.65%
      1/3/2022 0:0080.37%82.26%
      1/4/2022 0:0082.95%81.63%
      1/5/2022 0:0083.57%83.72%
      1/6/2022 0:0087.18%86.85%
      1/7/2022 0:0083.17%77.49%
      1/8/2022 0:0068.35%65.50%
      1/9/2022 0:0074.37%70.97%
      1/10/2022 0:0071.70%73.73%
      1/11/2022 0:0078.30%81.66%
      1/12/2022 0:0082.22%81.92%
      1/13/2022 0:0082.81%81.33%
      1/14/2022 0:0076.50%76.29%
      1/15/2022 0:0074.40%75.54%
      1/16/2022 0:0073.67%74.21%
      1/17/2022 0:0076.61%76.64%
      1/18/2022 0:0077.16%78.99%
      1/19/2022 0:0081.32%80.51%
      1/20/2022 0:0083.13%81.91%
      1/21/2022 0:0080.82%80.08%
      1/22/2022 0:0077.40%79.98%
      1/23/2022 0:0078.68%78.78%
      1/24/2022 0:0079.65%79.53%
      1/25/2022 0:0079.94%86.53%
      1/26/2022 0:0081.10%84.23%