Power Query - Filter Multiple Columns at once🦖😶‍🌫️

Подписаться 3,9 тыс.

Просмотров 1,7 тыс.

50% 1

In this video i show you how to use some list functions to quickly filter multiple columns in one go. #data #dataanalytics #powerbi #powerquery #excel #mquery 🦖😶‍🌫️
Follow along file:
github.com/jbo...
Functions Used
To add columns to a Table
Table.AddColumn
To check for specific values in lists
List.ContainsAny
To check for true or false matches in lists
List.MatchesAny

Опубликовано:

30 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 15

@SamehRSameh 2 месяца назад

Magic 🪄🪄 plz we need more about theses formula because no one explain it

@garywilhelm2299 Месяц назад

I like it. The first example is instructive in its use of the List.MatchesAny and Text.Contains functions, but there is an easier way to go to achieve same result. Just use List.Contains. More specifically, each List.Contains({[Stock], [RecentActivity], [PortfolioName]}, "none", Comparer.OrdinalIgnoreCase)),

@avinashverma52 2 месяца назад

Hi sir, I am Avinash from India. My problem is that I want to group my entire dataset (which I have already prepared in Power Query and set to connection only) based on one column. I want to display another column from the same dataset, which contains text descriptions, as a heading title for each group. I haven't found a video on the internet that shows how to do this. Please make a video on this.

@txreal2 2 месяца назад

Ok. What if in FilterMulti at each List.ContainsAny({[RecentActivity], [PortfolioName]}, {"Hold", "Bill Ackman"})), we also want "hold" and "bill Ackman"? Do we use each List.MatchesAny and an OR ?? Thanks. Please show me how :)

@danielgoncalveslima9350 2 месяца назад

You can use Comparer.OrdinalIgnoreCase, can works Well also...

@williamarthur4801 2 месяца назад

Hi, this might help; I have not botherd abotu upper lower case, but this is looking at the entire roow Table.AddColumn(Source, "Custom", each List.AllTrue( List.Transform( Record.ToList(_) , (A)=> List.ContainsAny( Splitter.SplitTextByWhitespace() (A) , {"a","b","c"} ) ))) and then change All to any if reuqured, I have also set up variable Recs = [ a = LIst.All , b = list.Any ] and thene : Table.AddColumn(Source, "Custom", each Function.Invoke( Record.Field( Recs, "a"),{ List.Transform( hope this gives yoiu a few ideas.

@garywilhelm2299 Месяц назад

No, All you have to do is use the existing function but add Comparer.OrdinalIgnoreCase as 3rd argument (to make it case-insensitive), so List.ContainsAny({[RecentActivity], [PortfolioName]}, {"Hold", "Bill Ackman"}, Comparer.OrdinalIgnoreCase)),