List Column Headers Of Columns In Which Text String Appears

[Syndicate_Admin]

Hi all, CGPT and Bing AI weren't helping, so I want to see if this sub can help.

So, I have the following table in Power Query:

column1 column2 column3

apple juice	pear	orange
apple	fig	apple cider
apple pie	apple	apple jam

, where the number of columns is dynamic and new columns might be added later.

How do I add a custom column showing the column headers in which the word "apple" (case-insensitive) appears in each row. For e.g., if "apple" shows up in columns 1 and 2 of row 1, corresponding cell in the custom column would be "column1, column2". If "apple" shows up in cols 1,3 and 4 of row 2, corresponding cell in custom column would be "column1, column3, column4" etc.. Please help?

[Syndicate_Admin]

Thank you for the explanation! This has been very helpful!

[Syndicate_Admin]

Sorry wdx223_Daniel, I'm tried breaking down your one-step formula into different steps to make myself learn better but I got stuck when trying to Text.Combine the [Name] column in the query titled Table1 (2). Do you mind if I send you the workbook for you to check what I did wrong?

[wdx223_Daniel]

Text.Combine needs a list arguement in the first place.

you can try Text.Combine({[Name]},",") ,this will give you a value ,eacatly same with [Name].

in Table1(2), you can not expand that table column, if you must do that, you need group your data in the last step, such as:

= Table.Group(#"Changed Type",Table.ColumnNames(Source),{"Custom",each Text.Combine([Name],",")})

or directly transform that table column instead of expand it, such as

= Table.TransformColumns(AddCustomColumn,{"Custom",each Text.Combine(Table.SelectRows(_,each Text.Contains([Value],"apple",Comparer.OrdinalIgnoreCase))[Name],",")})

[Syndicate_Admin]

Hi Syndicate_Admin,

Thanks for your help! As I'm very new to M code, may I ask why we don't convert the table to records first before turning the records to a table? Sorry, I'm having a hard time understanding how Table.SelectRows(Record.ToTable(_) works when there are no records in the previous step. The rest of your code, I understand.

[wdx223_Daniel]

When you add a column, each row is a record

[wdx223_Daniel]

=Table.AddColumn(YourTable,"Custom",each Text.Combine(Table.SelectRows(Record.ToTable(_),each Text.Contains([Value],"apple",Comparer.OrdinalIgnoreCase))[Name],","))