Forum Discussion
Get the first value of multiple days
Hello everyone,
I have data in power bi, having
Case 1 :
Before :
| NumFO | TimeObs | TimeReal |
| 13-april | 777154 | 90 | 100 |
| 14-april | 777154 | 90 | 27 |
| 15-april | 777154 | 90 | 0 |
Desired solution:
| NumFO | TimeObs | TimeReal |
| 13-april | 777154 | 90 | 100 |
| 14-april | 777154 | 0 | 27 |
| 15-april | 777154 | 0 | 0 |
I need to get the first date value if the "TimeObs" is spread over several days, regardless of the values in "TimeReal"
Is there a solution with the M language?
Thanks in advance
Hi Lazurens
See if this works for you, just copy the full script into a new blank query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWNTDRNTJW0lEyNzc3NDUBMiwNgIShgYFSrA5QgQkOBUbmEHlTHPJA7bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, NumFO = _t, TimeObs = _t, #"TimeReal " = _t]), ChType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"NumFO", Int64.Type}, {"TimeObs", Int64.Type}, {"TimeReal ", Int64.Type}}), GroupRows = Table.Group(ChType, {"NumFO"}, {{"t", each [ a = Table.Sort( _,{{"Date", Order.Ascending}}), b = Table.ReplaceValue(a,each [TimeObs],each if a[Date]{0} = [Date] then [TimeObs] else 0,Replacer.ReplaceValue,{"TimeObs"}) ][b] }}), Expand_t = Table.ExpandTableColumn(GroupRows, "t", {"Date", "TimeObs", "TimeReal "}, {"Date", "TimeObs", "TimeReal "}) in Expand_tGrouped the data on NumFO, included a sort just in case and updated the value in the TimeObs column.
with this result.
I hope this is helpful
2 Replies
- m_dekorte
Resident Rockstar
Hi Lazurens
See if this works for you, just copy the full script into a new blank query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWNTDRNTJW0lEyNzc3NDUBMiwNgIShgYFSrA5QgQkOBUbmEHlTHPJA7bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, NumFO = _t, TimeObs = _t, #"TimeReal " = _t]), ChType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"NumFO", Int64.Type}, {"TimeObs", Int64.Type}, {"TimeReal ", Int64.Type}}), GroupRows = Table.Group(ChType, {"NumFO"}, {{"t", each [ a = Table.Sort( _,{{"Date", Order.Ascending}}), b = Table.ReplaceValue(a,each [TimeObs],each if a[Date]{0} = [Date] then [TimeObs] else 0,Replacer.ReplaceValue,{"TimeObs"}) ][b] }}), Expand_t = Table.ExpandTableColumn(GroupRows, "t", {"Date", "TimeObs", "TimeReal "}, {"Date", "TimeObs", "TimeReal "}) in Expand_tGrouped the data on NumFO, included a sort just in case and updated the value in the TimeObs column.
with this result.
I hope this is helpful
- LazurensFrequent Visitor
I just had to re-adapt the solution to my data for confidentiality, but works like a charm : )
Thank you very much