cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OAkanbi
Frequent Visitor

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 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...

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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_

 

 

SU18_powerbi_badge

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.

 

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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_

 

 

SU18_powerbi_badge

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.

 

View solution in original post

OAkanbi
Frequent Visitor

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors