Calculated Column: Return a value only once on the same day for the same resource.

[Buckeyes2016]

Hello,

I am working to create a column in my PowerBI TimeEntry data table that returns the value 8 for "available hours" only once for a given employee on a given date. My data currently looks like the first three columns on the table below and I would like to create a 4th column that looks like the table below. I have tried several approaches and have not found something that works. Thank you so much for your help!

Resource	Date	Available Hours	Return
Employee 1	1/1/2023	8	8
Employee 1	1/1/2023	8
Employee 1	1/1/2023	8
Employee 1	2/1/2023	8	8
Employee 1	3/1/2023	8	8
Employee 1	3/1/2023	8
Employee 2	2/1/2022	8	8
Employee 2	2/1/2022	8
Employee 2	2/1/2022	8
Employee 2	4/1/2022	8	8
Employee 2	5/1/2022	8	8
Employee 2	5/1/2022	8

[Anonymous · Accepted solution]

Hi @Buckeyes2016 ,

You can follow the steps below to get it, please find the details in the attachment.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE1VMFTSUTLUN9Q3MjAyBjItlGJ1qCprhFfWmCxZI7wmUy5rglfWlGjZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, Date = _t, #"Available Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Resource", type text}, {"Date", type date}, {"Available Hours", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Resource", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Resource","Date","Available Hours"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Index"}, {"Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Index", "Return", each if [Index]=1 then [Available Hours] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Best Regards

[Buckeyes2016]

Here is a better view of what I am trying to return. Thanks!