Expression to lookup from another table in a CombineText loop

[barnestom]

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

[Anonymous]

Hi @barnestom,

Can you please share a pbix and some dummy data that keep raw data structure to test? It is hard to test the M query functions without any source data.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@barnestom , if you want to use two tables, then you need to merge them, have you done that?

refer if needed

https://exceloffthegrid.com/power-query-lookup-values-using-merge/

[barnestom]

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?