Have a Does Not Contain Problem.

[shawn3474]

Hi All.  I am new to PowerBI and am trying to create a dashboard and have run into a problem.

I have a table that lists project work with a list of teams in one column.  In another column I have a list of teams that have completed work on that project.   I am trying create a third column (Teams Work Still Remaining) in PowerBI that shows what teams still have work remaining.   I have tried using if([Teams]<>[Teams Work Completed],[Teams]) and that works for projects B and C but for anywhere where Teams Work Completed has multiple teams listed like for Project A below it does not work.

Project Name	Teams	Teams Work Completed	Teams Work Still Remaining
A	Web DBA Network	Web DBA	Network
B	Windows DBA	Windows	DBA
C	Unix DBA Web		Unix DBA Web

I have searched around and have not found any answers so far.  Any help is greatly appreciated.

[apo1979prio]

Table.AddColumn(Step20, "VP", each if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 

    List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 3, 3)))  then "E"

else if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 

    not(List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 3, 3))))  then "P"

else if List.Contains({"ZTOP","ZREP","ZG2P","ZGCP"},[DOC_TYPE]) then "F"  //Frota

else null)

[v-chuncz-msft]

@shawn3474,

You may start from Split Column By Delimiter in Query Editor.

[shawn3474]

@v-chuncz-msft,

Thank you for the response.  I am trying to work with that idea now but am wondering if you could expand on your thought a little more.  I think I see where you are going with this but am not 100% sure.

[shawn3474 · Accepted solution]

I think I found my own answer by simply changing the way the data is received.  It is a slight change of process but will eliminate the need for creating a lot of logic.  Thanks for the help.  Basically we will record all teams that are part of the project and remove them once the work is done rather than add them when the work is done.