Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

List Column Headers Of Columns In Which Text String Appears

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 juicepearorange
applefigapple cider
apple pieappleapple 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?

6 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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's avatar
      wdx223_Daniel
      Community Champion
      2 years ago

      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],",")})