Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
How can I have "Is Manager?" column output from the column "Employee Id" and "Manager Id"in power query?
Employee ID | Manager ID | Is Manager? |
100 | 200 | No |
200 | 200 | Yes |
300 | 200 | No |
400 | 700 | Yes |
500 | 700 | No |
600 | 700 | No |
700 | 700 | Yes |
800 | 400 | No |
900 | 400 | No |
1000 | 400 | No |
Solved! Go to Solution.
See this code @subirch
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMgKTfvlKsTrRUA6EjEwtBosZYyozAXPMUZSZIolBlZlhCplj0WkB5pkgK7PEFAK6FlUsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Manager ID" = _t, #"Is Manager?" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Manager ID", Int64.Type}}),
#"Added Is Manager" =
Table.AddColumn(
#"Changed Type", "Is Manager",
each if List.Contains(#"Changed Type"[Manager ID], [Employee ID]) then "Yes" else "No")
in
#"Added Is Manager"
It is a straight forward if/then/else construct with one function. The Key is this function: List.Contains(#"Changed Type"[Manager ID], [Employee ID])
It turns the Manager ID into a list, then scans that list to see if the Employee ID is contained in it, returning True or False.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee this code @subirch
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMgKTfvlKsTrRUA6EjEwtBosZYyozAXPMUZSZIolBlZlhCplj0WkB5pkgK7PEFAK6FlUsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Manager ID" = _t, #"Is Manager?" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Manager ID", Int64.Type}}),
#"Added Is Manager" =
Table.AddColumn(
#"Changed Type", "Is Manager",
each if List.Contains(#"Changed Type"[Manager ID], [Employee ID]) then "Yes" else "No")
in
#"Added Is Manager"
It is a straight forward if/then/else construct with one function. The Key is this function: List.Contains(#"Changed Type"[Manager ID], [Employee ID])
It turns the Manager ID into a list, then scans that list to see if the Employee ID is contained in it, returning True or False.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi Jakinta,
Below error is appearing, where "Changed Type" is prior step.
Expression.Error: We cannot convert the value "Changed Type" to type Table.
Details:
Value=Changed Type
Type=[Type]
Thank you
#"Changed Type"
= Table.AddColumn( PriorStepName , "Is Manager?", each if List.ContainsAny ( List.Distinct( PriorStepName [#"Manager ID"]), {[Employee ID]} ) then "Yes" else "No")
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.