Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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],","))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.