Forum Discussion

GabJohnson's avatar
GabJohnson
New Member
3 years ago
Solved

Converting ShiftID column to YrMonthDay custom column and Shift custom column

I am trying to add two customized columns in Power Query. I presently have a single column to identify a ShiftId (integer number) :

In this example, the digits that are presented reprensent the following (ShiftId 201014002) :
201014002 : Year 2020

201014002 : Month October

201014002 : Day 14

201014002 : Night Shift (other option 001 for Day Shift)

 

I am trying to have a column YearMonthDay that would be showing the Date in a YYYY-MM-DD format and anoter column Shift to indicate in text the Shift type. 

Example :
Column YearMonthDay : 2020-10-14
Column Shift : Night

Thank you for the help!

Need Help
  • Shaurya's avatar
    Shaurya
    3 years ago

    Hi GabJohnson,

     

    Here's the script for extracting shift code, setting Shift column value, extracting year, month and date and setting that to a Date type column:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNDQyNjAwVIrVAfMMDE0MDIyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Shift Code", each Text.Range([Value],6,3)),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Shift", each if [Shift Code] = "001" then "Day Shift" else "Night Shift"),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column", "YearMonthDay", each Text.Combine({"20",Text.Range([Value],0,2),"-",Text.Range([Value],2,2),"-",Text.Range([Value],4,2)})),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"YearMonthDay", type date}})
in
    #"Changed Type"

     

    Result:

     

     

    Mark this post as a solution if that works for you!

2 Replies

  • Shaurya's avatar
    Shaurya
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi GabJohnson,

     

    Here's the script for extracting shift code, setting Shift column value, extracting year, month and date and setting that to a Date type column:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNDQyNjAwVIrVAfMMDE0MDIyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Shift Code", each Text.Range([Value],6,3)),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Shift", each if [Shift Code] = "001" then "Day Shift" else "Night Shift"),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column", "YearMonthDay", each Text.Combine({"20",Text.Range([Value],0,2),"-",Text.Range([Value],2,2),"-",Text.Range([Value],4,2)})),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"YearMonthDay", type date}})
in
    #"Changed Type"

     

    Result:

     

     

    Mark this post as a solution if that works for you!

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    NewStep=Table.SplitColumn(PreviousStepName,"ShiftId",each {Date.From(Number.ToText(Number.IntegerDivide(_,1000),"00-00-00"),"zh"),{"Day","Night"}{Number.Mod(_,1000)-1}},{"Date","Shift"})