Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi.
I have a table of graduates and the degrees they've earned. Like so:
ID | DEGREE | YEAR | RESTRICTED |
123 | BA | 2020 | T |
123 | MS | 2022 | F |
121 | BS | 2000 | F |
111 | BBA | 1970 | F |
142 | MD | 1981 | F |
I'd like to generate a column in Power Query that returns T for all rows associated with ID 123. The business logic is that anyone with any restricted degree should show up as being restricted.
Thanks!
Solved! Go to Solution.
You can group by ID taking the max over RESTRICTED and then merge that back with the original table like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcnIEEkYGRgZAKkQpVgcm7hsMETcCUm5QcUOQeoi4gQFC3BAsDjbI0NIcScIEpNnXBSxuYQgRjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DEGREE = _t, YEAR = _t, RESTRICTED = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DEGREE", type text}, {"YEAR", Int64.Type}, {"RESTRICTED", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AnyRestricted", each List.Max([RESTRICTED]), type nullable text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Grouped Rows", {"ID"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"AnyRestricted"}, {"AnyRestricted"})
in
#"Expanded Grouped Rows"
You can group by ID taking the max over RESTRICTED and then merge that back with the original table like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcnIEEkYGRgZAKkQpVgcm7hsMETcCUm5QcUOQeoi4gQFC3BAsDjbI0NIcScIEpNnXBSxuYQgRjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DEGREE = _t, YEAR = _t, RESTRICTED = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DEGREE", type text}, {"YEAR", Int64.Type}, {"RESTRICTED", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AnyRestricted", each List.Max([RESTRICTED]), type nullable text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Grouped Rows", {"ID"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"AnyRestricted"}, {"AnyRestricted"})
in
#"Expanded Grouped Rows"
Ah, yep. After seeing @AlexisOlson's reply I understand the logic better.
I should read properly to start with. 😆
This will work.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Sorry, @KNP , that was my bad. I should've included my desired result column in that table. Thanks so much for taking the time to look at this.
Shouldn't the second row be 'T' also, or am I misunderstanding the logic.
Try this:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMjQyVtJRcnIEEkYGRgZKsTowMd9giJgRVMwQpA4iZgBVZwgWA2s2tDSHCpoYgTS7gMUsDJViYwE=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [ID = _t, DEGREE = _t, YEAR = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"ID", Int64.Type}, {"DEGREE", type text}, {"YEAR", Int64.Type}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Restricted",
each if [ID] = 123 then "T" else "F"
),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"Restricted", type text}})
in
#"Changed Type1"
Specifically, the 'Added Custom' step.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
You can easily do this in DAX
Column =
var MinYear =
CALCULATE(
MIN('Table'[Year]),
FILTER('Table',EARLIER('Table'[ID])='Table'[ID])
)
return
LOOKUPVALUE('Table'[Rest],'Table'[Year],MinYear)
The result
Thanks for the reply, @freginier . I'm trying to derive this field in Power Query because I'm creating a table in a dataflow, so it hasn't hit a .pbix at this point. Guess maybe I should just do it in SQL as part of the initial table load.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.