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

[OAkanbi]

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 solution does provide an answer to my request but I did not provide a dataset that would fufil another scenario that has popped up.

Essentially, I would like to transform this table:

Customer Date Status Overall_Rank Granular_rank
A 01/06/2021 Opt-In 1 1
A 10/06/2021 Opt-In 2 2
A 15/06/2021 OptOut 3 1
A 20/06/2021 Opt-In 4 3
A 30/06/2021 OptOut 5 2
F 01/06/2021 OptOut 1 1
F 02/06/2021 Opt-In 2 2
Z 10/06/2021 OptOut 1 1
Z 11/06/2021 Opt-In 2 2

Into this:

Customer Start End
A 01/06/2021 15/06/2021
A 20/06/2021 30/06/2021
F 02/06/2021 Current Date*
Z 11/06/2021 Current Date

*The current date being whatever the local date is.

The problem with the solution provided is that it does not take into consideration multiple customers. As such, the start and end dates get mixed up.

Any advice would be amazing.

J

Original request: https://community.powerbi.com/t5/Power-Query/Transforming-a-Single-Column-into-Two-Dates-Start-and-E...

[AlB · Accepted solution]

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.

[OAkanbi]

Fantastic stuff, AIB. Great solution and I've looked it through and it all makes sense.

You've opened my eyes up to functions which is a bonus. 5 stars.