Forum Discussion
Solved
Merge information row by row
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?
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"
1 Reply
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"