Merge information row by row

[RPAI2812]

Hi!

I would like to transform a dataset I have. Currently I have these 4 columns and normally (but not always) the values column "Day" and "Night" repeat once. Then I have the initial dates that could be kind of "merged". I mean, Initial date from week 1 and end date from week 2.

Is there a way I can create a new column "end date customized" which it shows the end date of the 2nd week if the "Day" value is the same between two consecutive rows?

[Payeras_BI · Accepted solution]

Hi @RPAI2812 ,

If understood well, you could group by Day (GroupKind.Local) and get the max of End date.

See below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLTNzTUNzIwMlbSUTIw0jc0gnH8cxIVwvSAjIDUotzEvNS8EgVDpVidaCUDYyRVBpbEaDE0QFJlaIasJSVFwRtVixFEizmSKiNjglpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Initial date" = _t, #"End date" = _t, Day = _t, Night = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Initial date", type date}, {"End date", type date}, {"Day", type text}, {"Night", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Day"}, {{"All", each _, type table [Initial date=nullable date, End date=nullable date, Day=nullable text, Night=nullable text]}, {"End date customized", each List.Max([End date]), type nullable date}},GroupKind.Local),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Initial date", "End date", "Night"}, {"Initial date", "End date", "Night"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All",{"Initial date", "End date", "Day", "Night", "End date customized"})
in
    #"Reordered Columns"