Forum Discussion

barnestom's avatar
barnestom
New Member
4 years ago

Expression to lookup from another table in a CombineText loop

Hi im trying to put an expression in a combinetext loop, im hitting a limitation I think but not sure why.

 

 

    #"AddTeamMembersTimesheetNames" = Table.AddColumn(#"AddTeamMembers", "TeamMembersTimesheetNames", each Combiner.CombineTextByDelimiter(";")
    (List.RemoveNulls(List.Transform([TeamMembers], 
    each if true then "wer" else "qwe" )))),

 

 

works and returns wer;wer;wer

 

What i want to do is lookup to see if they have a flag set against them on a different table (badly named as table) but am struggling to understand how to work around this.
Thought id start simple and just try and see if a name is on the other table but cant even get that to work.

 

 

#"ExpandTeam" =  Table.TransformColumns(#"ExpandTechnologies1", {{"Team", each if Value.Is(_, type list) then _ else {_} }} ),
#"AddTeamMembers" = Table.DuplicateColumn(ExpandTeam, "Team","TeamMembers"),

#"AddTeamMembersTimesheetNames" = Table.AddColumn(#"AddTeamMembers", "TeamMembersTimesheetNames", 
    each Combiner.CombineTextByDelimiter(";")
   (List.RemoveNulls(List.Transform([TeamMembers], 
    each if (Table.RowCount(Table.SelectRows(Table, each [Employee] = "Matching String")) > 0) 
    then "wer" else "qwe" )))),

TeamMembersTimesheetNames = AddTeamMembersTimesheetNames{0}[TeamMembersTimesheetNames]
in
    TeamMembersTimesheetNames

 

 

result

 

Need Help

3 Replies

    • barnestom's avatar
      barnestom
      New Member
      4 years ago

      Hi thanks for the reply, no i havent done that.

      My understanding is that that would only work if there was a column to match/join on?

      I have a variable length list of records which contain names in each row on my project table and want to look up to the other table which has each name and a flag to say if they should be shown.

       

      Interstingly the List of names turns into the string [List] after converting it to a csv string in a new column as well but anyway. But i thought just using the values of one table to effectivly run a subquery on another table might work.

      Not sure i explained this very well but This is what i start with

      each list contains records (this is expanded out into multiple rows)

       

       

       

      im trying to get to a new column with a string of

      bob;fred;carl;

      based on a column on the second table saying if they should be shown

       

       

      I can do it for all of the names but am struggling with getting an expression in the if.

       

      Maybe I should be expanding the list into multiple rows and extracing the field out of the record, joining on the name / merging then remving rows that i dont need then trying to collapse them back into one row that has a list to then do the combine?