Forum Discussion

ccianci's avatar
ccianci
Frequent Visitor
4 years ago
Solved

Fixing inconsistent names & missing titles

Hello all,   I have two issues I'm hoping you can help me with.  I'm fairly new to Power BI & Power Query and i'm working on a PBI report comparing joiners vs leavers over the last x years.  For yo...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi ccianci ,

     

    You may add a new column:

    Filled Title = 
var _title= CALCULATE(MAX('Table'[Title]),ALLEXCEPT('Table','Table'[Email]))
return IF([Title]=BLANK(),_title,[Title])

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ccianci's avatar
ccianci
Frequent Visitor
4 years ago

Thank you both.  Here's a sample data (cleaned for privacy) of what I'm talking about:

 

ItemFull NameNameEmailTitleStart DateLeaving Date
JoinerDoe, John [email protected]Vice President2022-05-16 
JoinerLow, Karen [email protected]Consultant2022-03-21 
Leaver Karen Low[email protected]  2022-05-15

 

First issue: Leaver title is missing (line 4) but since that same employee joined this year (line 3).  Is there a way to do a search in Power Query (or dax) to fill in that title?  If the search returns nothing, then leave the title blank.

 

Second issue: cancel this one - i figured out a solution using Power Query