Forum Discussion

seedrs91's avatar
seedrs91
Frequent Visitor
2 years ago
Solved

List column headers of columns in which string appears

Hi all, CGPT and Bing AI kept producing bad code, so I want to see if this forum can help. 

So, I have the table

 

| 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" is in columns 1 and 2 of row 1, corresponding cell in custom column would be "column1, column2". If "apple" is in cols 1,3 and 4 of row 2, corresponding cell in custom column would be "column1, column3, column4" etc.. Please help?

Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi seedrs91 

    Here's an example of how you could do it. Paste this into a blank empty query in the Advanced Editor.

     

    The final step Added Columns with Apple is the important one.

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WSiwoyElVyCrNTE5V0lEqSE0sAlL5RYl56alKsTpQeaBQWmY6kISoTs5MSS1CyCoUZKbC5OBqshJzlWJjAQ==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [column1 = _t, column2 = _t, column3 = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"column1", type text}, {"column2", type text}, {"column3", type text}}
  ),
  #"Added Columns with Apple" = Table.AddColumn(
    #"Changed Type",
    "Columns with Apple",
    each Text.Combine(
      Table.SelectRows(Record.ToTable(_), each Text.Contains(Text.Lower([Value]), "apple"))[Name],
      ", "
    ),
    type text
  )
in
  #"Added Columns with Apple"

     

    Regards

     

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 years ago

    Hi seedrs91 

    Here's an example of how you could do it. Paste this into a blank empty query in the Advanced Editor.

     

    The final step Added Columns with Apple is the important one.

    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WSiwoyElVyCrNTE5V0lEqSE0sAlL5RYl56alKsTpQeaBQWmY6kISoTs5MSS1CyCoUZKbC5OBqshJzlWJjAQ==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [column1 = _t, column2 = _t, column3 = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"column1", type text}, {"column2", type text}, {"column3", type text}}
  ),
  #"Added Columns with Apple" = Table.AddColumn(
    #"Changed Type",
    "Columns with Apple",
    each Text.Combine(
      Table.SelectRows(Record.ToTable(_), each Text.Contains(Text.Lower([Value]), "apple"))[Name],
      ", "
    ),
    type text
  )
in
  #"Added Columns with Apple"

     

    Regards