Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
Solved! Go to 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
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
@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
@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
@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
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?
@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?
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
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
@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
I looked at your formula again and did not find there sorting by the "length" parameter. Will it return the correct value for sure?
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
85 | |
66 | |
52 | |
47 |
User | Count |
---|---|
215 | |
90 | |
83 | |
66 | |
58 |