List Column Headers Of Columns In Which Text String Appears

(0) Share

Report

Posted on by seedrs91

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?

Categories:

Power Query

I have the same question (0)

All responses (3)

Answers (0)

Sort by

seedrs91 6 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
seedrs91 6 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry wdx223_Daniel, I'm tried breaking down your one-step formula into different steps to make myself learn better but I got a column of all error values 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?

Find Headers Wher...

Was this reply helpful? Yes No
seedrs91 6 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No