Converting ShiftID column to YrMonthDay custom column and Shift custom column

[GabJohnson]

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!

[wdx223_Daniel]

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"})

[Shaurya · Accepted solution]

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!