Forum Discussion

paul_luvaglia's avatar
paul_luvaglia
Regular Visitor
9 months ago
Solved

Most Recent Transaction

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 BookingOperationPart NumberStdTimeCurrent StdTime
1Weld123XYZ2327
2Weld123XYZ2327
3Weld123XYZ2327
4Weld123XYZ2327
5Weld123XYZ2527
6Weld123XYZ2527
7Weld123XYZ2527
8Weld123XYZ2727
9Weld123XYZ2727
10Weld123XYZ2727

 

Many Thanks

  • 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"

     

6 Replies

  • 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. 

  • 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"

     

  • 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.

  • 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