Most Recent Transaction

[paul_luvaglia]

Hi All,

Appreciate any help I can get on this one

I have an imported table in PBI as below (columns 1-4)

I want to add the 5th column which takes the StdTime from the highest "Labour Booking" for the combination of "Operation" and "Part Number" - in the example below this would be 27

Labour Booking	Operation	Part Number	StdTime	Current StdTime
1	Weld	123XYZ	23	27
2	Weld	123XYZ	23	27
3	Weld	123XYZ	23	27
4	Weld	123XYZ	23	27
5	Weld	123XYZ	25	27
6	Weld	123XYZ	25	27
7	Weld	123XYZ	25	27
8	Weld	123XYZ	27	27
9	Weld	123XYZ	27	27
10	Weld	123XYZ	27	27

Many Thanks

[v-nmadadi-msft]

Hi @paul_luvaglia 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @paul_luvaglia 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[raisurrahman]

@paul_luvaglia :

@ronrsnfld solution works. I have tested it. 

[ronrsnfld · Accepted solution]

Here is one way.

It assumes that, unlike your example, there will be more than a single combination of Operation/Part Number.

• Group by Part Number/Operation
• Select StdTime corresponding to the Max Labour Operation

let

//Change Source line to reflect your actual data source
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpPzUkBUoZGxhGRUUCGkbFSrE60khFuKWPcUia4pUyxSZmCpcxwS5njlrLAJmUOlrLELWVogEMuFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Labour Booking" = _t, Operation = _t, #"Part Number" = _t, StdTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Labour Booking", Int64.Type}, {"Operation", type text}, {"Part Number", type text}, {"StdTime", Int64.Type}}),
    
    #"Add Current StdTime" = Table.Group(#"Changed Type", {"Operation", "Part Number"}, {
        {"Current StdTime", (t)=> Table.AddColumn(t, "Current StdTime", each 
            [a=Table.SelectRows(t, each [Labour Booking] = List.Max(t[Labour Booking])),
             b=a[StdTime]{0}][b]),
            type table[Labour Booking=text,Operation=text, Part Number=text, StdTime=Int64.Type, Current StdTime=Int64.Type]
            }}),

    #"Removed Columns" = Table.RemoveColumns(#"Add Current StdTime",{"Operation", "Part Number"}),
    #"Expanded Current StdTime" = Table.ExpandTableColumn(#"Removed Columns", "Current StdTime", 
        {"Labour Booking", "Operation", "Part Number", "StdTime", "Current StdTime"})
in
    #"Expanded Current StdTime"

[tayloramy]

Hi @paul_luvaglia, as @raisurrahman asked, if you can provide the expected output, we can write some quick DAX/M for you. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[raisurrahman]

@paul_luvaglia 
Could you share a sample of the expected output? That will help me write the code.