Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Thank you for the explanation! This has been very helpful!
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?
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],",")})
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.
When you add a column, each row is a record
=Table.AddColumn(YourTable,"Custom",each Text.Combine(Table.SelectRows(Record.ToTable(_),each Text.Contains([Value],"apple",Comparer.OrdinalIgnoreCase))[Name],","))