Forum Discussion

OAkanbi's avatar
OAkanbi
Frequent Visitor
5 years ago
Solved

Transforming a Single Column into Two Dates - Start and End

Hi There, I'm having trouble converting a single column into a start and end date column using power query. The sticking point is that the column Status is used as an indicator for the end date. H...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dcw0zcyMDIEcvwLSnQ984AMQzCO1YGoMTTApsYIjOFqTFHV+JeWABnGKOYYYTXHBKwOpsbYAJs5phC7YgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, Status = _t, Overall_Rank = _t, Granular_rank = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Overall_Rank", Int64.Type}}),
    Pos = List.Transform(List.PositionOf(#"Changed Type"[Status], "out", Occurrence.All, (x,y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)), each _+2),
    Grouped = Table.Group(#"Changed Type", {"Customer", "Overall_Rank"}, {"ar", each let l=[Date] in [Start=l{0}, End=List.Last(l)]}, 0, (x,y) => Number.From(List.Contains(Pos, y[Overall_Rank]))),
    #"Expanded ar" = Table.RemoveColumns(Table.ExpandRecordColumn(Grouped, "ar", {"Start", "End"}, {"Start", "End"}), "Overall_Rank")
in
    #"Expanded ar"

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dcw0zcyMDIEcvwLSnQ984AMQzCO1YGoMTTApsYIjOFqTFHV+JeWABnGKOYYYTXHBKwOpsbYAJs5phC7YgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, Status = _t, Overall_Rank = _t, Granular_rank = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Overall_Rank", Int64.Type}}),
    Pos = List.Transform(List.PositionOf(#"Changed Type"[Status], "out", Occurrence.All, (x,y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)), each _+2),
    Grouped = Table.Group(#"Changed Type", {"Customer", "Overall_Rank"}, {"ar", each let l=[Date] in [Start=l{0}, End=List.Last(l)]}, 0, (x,y) => Number.From(List.Contains(Pos, y[Overall_Rank]))),
    #"Expanded ar" = Table.RemoveColumns(Table.ExpandRecordColumn(Grouped, "ar", {"Start", "End"}, {"Start", "End"}), "Overall_Rank")
in
    #"Expanded ar"

  • OAkanbi's avatar
    OAkanbi
    Frequent Visitor
    5 years ago

    Perfect. Brilliant.

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      5 years ago

      Here's a generic solution to your senario, which leverage an index column instead of Overall_Rank.

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dcw0zcyMDIEcvwLSnQ985RidSByhgZ45ExR5fxLS+ByRnj0GRtg0RcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, Status = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Pos = List.Transform(List.PositionOf(#"Added Index"[Status], "out", Occurrence.All, (x,y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)), each _+1),
    Grouped = Table.Group(#"Added Index", {"Customer", "Index"}, {"ar", each let l=[Date] in [Start=l{0}, End=List.Last(l)]}, 0, (x,y) => Number.From(List.Contains(Pos, y[Index]))),
    #"Expanded ar" = Table.RemoveColumns(Table.ExpandRecordColumn(Grouped, "ar", {"Start", "End"}, {"Start", "End"}), "Index")
in
    #"Expanded ar"