Forum Discussion

OAkanbi's avatar
OAkanbi
Frequent Visitor
5 years ago
Solved

Transforming a Single Column into Two Dates Version 2- Start and End

Hey There,   First of all, apologies if I am not following forum protocol. I posted for help a few days ago with this problem, and I thought I had recieved a solution so I marked it as solved. The ...
  • AlB's avatar
    5 years ago

    Hi OAkanbi 

     See it all at work in the attached file. Place the following M code in a blank query to see the steps:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfIvKNH1zAMyDAz1Dcz0jQyMDJVideBy/qUlQIahKTY5iD4jA9z6jFHl3JDl0OxzQ3GLEYpcFIpbDLDJQfQZIpkZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Status = _t, Date = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Status", type text}, {"Date", type date}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer", Order.Ascending}, {"Date", Order.Ascending}}),
        #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Customer"}, {{"Grouped", each _}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each arrangeF_([Grouped])),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Status", "Date", "Custom.1"}, {"Status", "Date", "Custom.1"}),
        #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Status]), "Status", "Date"),
        #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each not ([#"Opt-In"] = null and [#"OptOut"] <> null)),
        #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Opt-In", type date}, {"OptOut", type date}}),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null, Date.From(DateTime.LocalNow())  ,Replacer.ReplaceValue,{"OptOut"})
    in
        #"Replaced Value"

     

    The code above uses this function:

     

    (inputT as table) =>
    let 
        #"Added Index" = Table.AddIndexColumn(inputT, "Index.1", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let aux_ = #"Added Index"[Status], previous_ = try aux_{[Index.1] - 1} otherwise null, res_ = if previous_ = "Opt-In" and [Status]="OptOut" then 0 else 1 in res_, Int64.Type),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let aux_ = #"Added Custom"[Custom] in 0+List.Sum(List.Range(aux_,0,[Index.1]+1))),
        res_ = Table.RemoveColumns(#"Added Custom1",{"Index.1", "Custom"} )
    in 
        res_

     

     

    Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.