Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Level 1", each if [Manager4_Name]= null then [Manager3_Name] else [Manager4_Name]),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Level1a", each if [Level 1] = null then [Manager2_Name] else [Level 1]),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Level1b", each if [Level1a] = null then [Manager1_Name] else [Level1a]),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Level 1c", each if [Level1b] = null then [ReportingTo] else [Level1b])
Hi All
I have added 4 Steps above to create a Column with highest non null Value across 4 Columns
Manager4_Name Manager3_Name Manager2_Name Manager1_Name ReportingTo
John Smith
null David Smith Pete Smith
null null Rob Smith Jackie Smith Geraint Smith
I woul like to get
Level 1
John Smith
David Smith Rob Smith
My Steps above appear to achieve this, but can this be done in one Step?
Solved! Go to Solution.
Hi @derekmac
I suppose it will be a matter of adding a couple of "ifs". Try it:
= Table.AddColumn(#"Removed Columns", "Level 2", each if [Manager4_Name]<>null then [Manager3_Name]??[Manager2_Name]??[Manager1_Name]??[#" ReportingTo"] else if [Manager3_Name]<>null then [Manager2_Name]??[Manager1_Name]??[#" ReportingTo"] else if [Manager2_Name]<>null then [Manager1_Name]??[#" ReportingTo"] else if [Manager1_Name]<>null then [#" ReportingTo"] else null)
=List.Skip(Record.ToList(_),each _=null){0}?
=List.Skip(Record.ToList(_),each _=null){1}?
Hi @derekmac
Yes, you can do it using this:
= Table.AddColumn(#"Removed Columns", "Level 1", each [Manager4_Name]??[Manager3_Name]??[Manager2_Name]??[Manager1_Name]??[#" ReportingTo"])
Thanks a lot
How would I replicate this for Level 2 so in the example above I get Pete Smith from the middle line? and Jackie Smith in the 3rd line?
Hi @derekmac
I suppose it will be a matter of adding a couple of "ifs". Try it:
= Table.AddColumn(#"Removed Columns", "Level 2", each if [Manager4_Name]<>null then [Manager3_Name]??[Manager2_Name]??[Manager1_Name]??[#" ReportingTo"] else if [Manager3_Name]<>null then [Manager2_Name]??[Manager1_Name]??[#" ReportingTo"] else if [Manager2_Name]<>null then [Manager1_Name]??[#" ReportingTo"] else if [Manager1_Name]<>null then [#" ReportingTo"] else null)