Power query help to distribute hours

[vjnvinod]

Hi,

I am trying to achieve a condition in power query and your help will be highly appreciated

if my Employe GUI  has same transaction date , i will have to put 8 hours to one of those transaction date and other repeated transaction date should be kept 0

how do i achieve this in power query?

[v-eqin-msft · Accepted solution]

Hi @vjnvinod ,

Sorry for my late reply.

According to my understand, for rows with the same emp and date, you want to choose one to give the value of 8h and the rest is 0, right?

All I do is to rank by Emp and Date and then use #duration(0,8,0,0) to set 8h when rank=1

Here’s the full M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNjYzMVbSUTIyMDLQNzTSNzRXitUhT8LIyNTChDIJMzwSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee GUI" = _t, #"Transaction Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee GUI", Int64.Type}, {"Transaction Date", type date}}),   
    #"Grouped" = Table.Group(#"Changed Type", {"Employee GUI","Transaction Date"}, {{"AllRows", each _, type table}}),
    RankFunction = (tabletorank as table) as table =>
     let
      SortRows = Table.Sort(tabletorank,{{"Transaction Date", Order.Descending}}),
      AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1)
     in
      AddIndex,
    #"Added Index" = Table.AddIndexColumn(#"Grouped", "Rank", 1, 1, Int64.Type),
    #"Group Rows" =Table.TransformColumns(#"Added Index", {"AllRows", each RankFunction(_)}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Group Rows", "AllRows", {"Rank"}, {"AllRows.Rank"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "Hour", each if [AllRows.Rank]=1 then #duration(0,8,0,0) else #duration(0,0,0,0))
in
    #"Added Custom"

The final output is shown below:

You could take a look at the pbix here.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Eyelyn Qin

[Greg_Deckler]

@vjnvinod - Sorry, first, can you post that as text? Second, can you post what you are expecting as output?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.