Take maximum value from another table with duplicate values

[Anonymous]

Good day.
I have two tables, it is in different files, but I copied them into an Excel file in order to show it.
I have a blue table, I need to add an Fauilt code from the red one to each row of the blue table, but at the same time, I need to take the code that has the max Length. If there is no such row in the red table, then simply leave a blank in the cell of the blue table.

It turns out that you need to first get all the rows with the same key, then sort inside the formula, and then display the first one in the list?

[AlB · Accepted solution]

@Anonymous 

Simple answer:

1. You didn't specify that

2. I didn't look in detail

Simplified version:

New column V2 = 
VAR auxT_ =
    CALCULATETABLE (
        'Reel defect',
        TREATAS ( CALCULATETABLE ( DISTINCT ( 'Reel status'[Key] ) ), 'Reel status'[Key] )
    )
VAR maxLenT_ = TOPN ( 1, auxT_, [Length], DESC )
RETURN
    MAXX ( maxLenT_, [Fault code] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

If you need it in PQ (which you didn't specify at the beginning  either), place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFBCoUwDIThu3StYDOJ6FnE+1/jKbwWZ5KFFORb/J1eV9v67m1p/f/ZuW/P8f5d+9rbvRCxREwJEoEST8SVRCLxJTZz4xzEONdm7peYEiQCJZ6IK4lEKBfFuuBcFOuCc1GsC85FsS44F8W64NzxCMcxhXPtuDcJUwEVUOEqXEWoeErvHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, #"Runup Sequence" = _t, Lane = _t, #"Reel Lenght" = _t, Key = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"Runup Sequence", Int64.Type}, {"Lane", Int64.Type}, {"Reel Lenght", Int64.Type}, {"Key", type text}}),

    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Key"}, #"Reel defect", {"Key"}, "Reel defect", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Fault code", (input)=> try let max_ = List.Max(input[Reel defect][Length]), pos_ = List.PositionOf(input[Reel defect][Length], max_, Occurrence.First), res_ = input[Reel defect][Fault code]{pos_} in res_ otherwise null, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Reel defect"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

If you need it in PQ (which you didn't specify at the beginning  either), place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFBCoUwDIThu3StYDOJ6FnE+1/jKbwWZ5KFFORb/J1eV9v67m1p/f/ZuW/P8f5d+9rbvRCxREwJEoEST8SVRCLxJTZz4xzEONdm7peYEiQCJZ6IK4lEKBfFuuBcFOuCc1GsC85FsS44F8W64NzxCMcxhXPtuDcJUwEVUOEqXEWoeErvHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, #"Runup Sequence" = _t, Lane = _t, #"Reel Lenght" = _t, Key = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"Runup Sequence", Int64.Type}, {"Lane", Int64.Type}, {"Reel Lenght", Int64.Type}, {"Key", type text}}),

    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Key"}, #"Reel defect", {"Key"}, "Reel defect", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Fault code", (input)=> try let max_ = List.Max(input[Reel defect][Length]), pos_ = List.PositionOf(input[Reel defect][Length], max_, Occurrence.First), res_ = input[Reel defect][Fault code]{pos_} in res_ otherwise null, type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Reel defect"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

 You haven't shown the expected result so you might have to tweak the code yourself.  Can be done in PQ as well but here is a DAX solution for a calc column in the Reel status table:

New column =
VAR auxT_ =
    CALCULATETABLE (
        'Reel defect',
        TREATAS (
            CALCULATETABLE (
                SUMMARIZE (
                    'Reel defect',
                    'Reel defect'[Order],
                    'Reel defect'[Runup Sequence],
                    'Reel defect'[Lane]
                )
            ),
            'Reel status'[Order],
            'Reel status'[Runup Sequence],
            'Reel status'[Lane]
        )
    )
VAR maxLenT_ =
    TOPN ( 1, auxT_, [Length], DESC )
RETURN
    MAXX ( maxLenT_, [Fault code] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Yes, it's still not Power Query, but it works.

Tell me, why did you use Order, Runup and Line, and not use the Key that is already in the table?

[AlB · Accepted solution]

@Anonymous 

Simple answer:

1. You didn't specify that

2. I didn't look in detail

Simplified version:

New column V2 = 
VAR auxT_ =
    CALCULATETABLE (
        'Reel defect',
        TREATAS ( CALCULATETABLE ( DISTINCT ( 'Reel status'[Key] ) ), 'Reel status'[Key] )
    )
VAR maxLenT_ = TOPN ( 1, auxT_, [Length], DESC )
RETURN
    MAXX ( maxLenT_, [Fault code] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

So. I use it, but now i get some error.

I am trying to create a new column in the 'Reel status' table and write a condition there that depends on the created column. I get the error A circular dependency was detected: Reel status [Fault code], Reel status [NewColumn], Reel status [Fault code].
After all, the created column takes values from 'reel defect', how can changes in reel status affect it?

NewColumn = 'Reel status'[Fault code]

[AlB]

Hi @Anonymous 

Please show both tables above in text-tabular format so that the contents can be copied. I'll then help with a PQ solution.  Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data). Show also the table with the final expect result for that data.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Reel status
Order Runup Sequence Lane Reel Length Key
0164 1 1 2960 0164-1-1
0164 1 2 2960 0164-1-2
0164 1 3 2960 0164-1-3
0164 1 4 2960 0164-1-4
0164 1 5 2960 0164-1-5
0164 2 1 2959 0164-2-1
0164 2 2 2959 0164-2-2
0164 2 3 2959 0164-2-3
0164 2 4 2959 0164-2-4
0164 2 5 2959 0164-2-5
0164 3 1 2960 0164-3-1
0164 3 2 2960 0164-3-2
0164 3 3 2960 0164-3-3
0164 3 4 2960 0164-3-4
0164 3 5 2960 0164-3-5
0164 4 1 880 0164-4-1
0164 4 2 880 0164-4-2
0164 4 3 880 0164-4-3
0164 4 4 880 0164-4-4
0164 4 5 880 0164-4-5

Reel defect
Order Runup Sequence Lane Fault Code Reason Code Length Reel Status Key
0164 1 1 100-07 1DA00 105 Doctor 0164-1-1
0164 1 1 100-07 1DA00 1 Doctor 0164-1-1
0164 1 3 212-04 30000 1 Doctor 0164-1-3
0164 3 3 316-10 30000 30 Doctor 0164-3-3
0164 3 3 212-04 30000 1 Doctor 0164-3-3
0164 5 4 590-01 5HA00 1 Doctor 0164-5-4

[amitchandak]

@Anonymous , One is to merge in power query, if need create a concatenated key to have join and the get the required column

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

In DAx Copy value from one table to another -https://www.youtube.com/watch?v=czNHt7UXIe8

example 

maxx(filter(defect, defect[order] = status[order] && defect[runup sequence] = status[runup sequence] && defect[lane] = status[lane] && defect[key] = status[key]),defect[Fault code])

add remove column as per need in above

[Anonymous]

I looked at your formula again and did not find there sorting by the "length" parameter. Will it return the correct value for sure?

[Anonymous]

Yes, I have a key "key" that is unique in the Status table and is associated in the model with the key in Defect.
DAx, that's good, but is there a way to do it in Power Query?