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!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors