Forum Discussion
OAkanbi
5 years agoFrequent 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 ...
- 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.
AlB
5 years agoCommunity Champion
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. |
- OAkanbi5 years agoFrequent 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.