Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Lazurens
Frequent Visitor

Get the first value of multiple days

Hello everyone, 

 

I have data in power bi, having 

 

Case 1 : 

Before : 

 

NumFOTimeObsTimeReal 
13-april77715490100
14-april7771549027
15-april777154900

Desired solution:

 

NumFOTimeObsTimeReal 
13-april77715490100
14-april777154027
15-april77715400

 

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

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

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_t

 

Grouped the data on NumFO, included a sort just in case and updated the value in the TimeObs column.

with this result.

m_dekorte_0-1681459624439.png

 

I hope this is helpful

 

View solution in original post

2 REPLIES 2
m_dekorte
Super User
Super User

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_t

 

Grouped the data on NumFO, included a sort just in case and updated the value in the TimeObs column.

with this result.

m_dekorte_0-1681459624439.png

 

I hope this is helpful

 

I just had to re-adapt the solution to my data for confidentiality, but works like a charm : ) 

Thank you very much

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors