Forum Discussion
Take maximum value from another table with duplicate values
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
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
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
Community ChampionAnonymous
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
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
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
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]