The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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?
Solved! Go to 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"
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"