Це відео не доступне.
Перепрошуємо.
Filter Data with Multiple Criteria using Power Query
Вставка
- Опубліковано 29 кві 2019
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video I'll share with you how to apply multiple dynamic filters using Excel or Power BI on your data.
Other excel filter tricks - www.goodly.co.in/filter-data-...
Make a Dashboard using Advanced Filter - www.goodly.co.in/make-a-dashb...
Make a Dashboard in 15 Mins - www.goodly.co.in/make-a-dashb...
- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
- - - - Blog - - - -
www.goodly.co.in/blog
This is a great video! However if the filter column is empty, it returns no data. Do you know how to fix it please?
Check out this excel file - chandeepchhabra-my.sharepoint.com/:x:/g/personal/chandeepchhabra_chandeepchhabra_onmicrosoft_com/EQBkbtlj7rFNvekCDoLpk5wBTy-00f89Wy9nh0mTGDIyKQ?e=VATxI9
@@GoodlyChandeep thanks!
@@TheRoseFloyd Good question!
@@GoodlyChandeep Dear Goodly, thank you so so much for this tutorial, it has been awesome! Currently I am having problems in filtering when one of the columns is empty (=take everything that appears) is that even possible? thank you so much in advance!
a tip: you can watch movies at InstaFlixxer. Me and my gf have been using it for watching all kinds of movies recently.
THIS is how a video should be done. Quick intro of what to expect, clear and concise delivery of the promised content. Approaches for differentiate cases covered. Great accent and easy to follow. Subscribed! :)
Thanks for your kind words
I'm Vietnamese, I'm from Asia, thank you very much for your shared posts, it is very useful for my work. Wish you good health
thanks, as a newbi with power query, I spent long hours today trying to figure out how to apply multiple filters until I came across this video. Will try it on Monday! Now that I found this video I can finally relax for the weekend!
That is such a flattering comment :)
Glad I could help!
Brilliant video! Exactly what I was looking for.
Thanks Dhruv, Glad you liked it:)
Very well explained
That was an amazing tutorial. I am new to Power Query and learning every day. thanks for sharing your knowledge.
Fantastic..
Good informative video. I was searching for it. Happy as well sad. Sad coz I wanted 2-3 days thinking parameters (after converting to list) can filter multiple criteria.
Excellent, very Useful
Excelent video. But, in third case, what about if I want all the occurrences of that 3 sales rep in north or south region? That's mean: Rajat in north or sout; Swati in north or south and Veronica in north or south region. Should I place all possible combinations between them in the filter table? or there is another method to do that ?
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Great video!!
So efficient tutorial so many thanks
Glad you like it 😊
Thanks!
Thanks a lot :)
Hi - Thanks, really good video. Is there any chance you know if when using multiple filters you can toggle so that a filter is either active/inactive?
Great video. This is very useful. Thanks for sharing
You are very welcome
How to filter the partial matches also
Thank you this is very helpful
Thanks Kiris
Amazing video!!! Thanks a lot
Glad it helped
This is a great tutorial. However, I have different filter problem that I dont know how to achieve it. I am hoping you can help me with it. Thanks
If you are interested in creating a concatenated join, I recommend skipping to minute 8:54 of the tutorial where you will learn how to filter based on multiple criteria from different columns.
your signature of automation is missing for the filter condtion. :-) , got it by extracting the column names from the filter2 and applied it in the merge queries step. The pinned one for empty criteria it is possible to use Table.IsEmpty function. (Still the query may retrun nothing if a space character is put in the condition). It is ok for a small data set in Excel if data is loaded to sheet.Now a regular visitor of this channel. Thanks for the contents.
Hi friend!! Greetings from Colombia!
In that same example, is it possible to do the same filter for several criteria using the "Date" field?
for example I would like to filter 2 criteria and in the "Date" field and generate a date range.. in your example will be 14-03-2006 to 12-08-2008 for Rajat
Nice
Thanks!
I am trying to filter with a number greater than or equal to. However I am not able to locate a video explaining the same. Can you tell which function we will use. I understand list.contans is the signed for searching the text only.
Can you please reslove my query as i am stuck in the merge step
1) I have two tabs name called as Company and Structure
2) I want to do vlookup i.e. merger from structure tab to company tab to find out only Unique ID i.e. ABC, DEF and GHI.
3) While apply meger step i found that power query added those rows which were not available in company tab
4 ) for example In structure tab there are 3 rows with unique ID i.e. row 1 ABC row 2 CDE row 3 FGH however in Company tab there were only 2 unique ID in row 1 ABC row 2 def
5) Now power query added new row with name EFG however there were only 2 rows fir vlookup
6) I want to know how to fix this issue so that it won't create extra rows.
sir this can do it in pivot table
Great, Thank you. Could you please show an example if we need to apply dynamic filter on date for today().Today() date don't be link in excel.i have tried to put today() function in power query but it won't work.Is there any way to do that?
In Power Query the function is DateTime.LocalNow()
I have one question when I apply SUMIFS in the power query editor in power bi according to your direction so the problem is I have a huge amount of rows when I apply this operation then the system takes a lot of time. so please tell me what I do in this condition if you have any other way to apply sumifs please tell me.
When I use filter and I work then I see that previous item did not show.like if I have 3 item in the column ,India,Delhi, noida if I select Delhi and I use any condition after that I can not go to The other filter items.
thanks for sharing.
At this point I have a problem.
I am using a BD Analysis Services and I intend to filter on reading the data by dates but I am not getting it.
I'm using something like this:
{Cube.ApplyParameter, "DateRange", {#date (2019, 6, 13), #date (2019, 6, 13)}}
The error it gives me:
"Cannot convert a value of type List to type Function"
Can you help me?
Thank you for everything
Is there any solution to vlookup from multiple value cell. Like A1 =25, A2=7|25, A3=0, A5=0|7|25..desired result B1, B2, B5 should be mark as positive match...pls guide
excellant si ji😈😈😈
Please note that the merge method with multiple filters will only efficiently work with smaller datasets. You will wreck your workbook if you merge with a large dataset.
Brilliant stuff, where is the file link to this video could you please share
goodly.co.in/filter-data-with-multiple-criteria-power-query/
@@GoodlyChandeep thanks
Bro, How to use same methods for filter dates pls comment TIA
Please elaborate on your question!
I have fetched data with power query in the fetched data first column with multiple dates I need to filter only for specific date (01- 04-2020) using reference cell which we are provide
@@karthickenigoo2476 Download the solution - chandeepchhabra-my.sharepoint.com/:x:/g/personal/chandeepchhabra_chandeepchhabra_onmicrosoft_com/EVaOXPwG2cROi1xdkmRv0KcB6FJa5380XmmcSiJr_vgGmw?e=Pe28LO
Goodly Hi, Thank you for all videos. I learned a lot from them. But I have still getting a problem to filter my Datasets.
I am working on very big Dataset( balance sheet) of one private Bank. I have build my report Date table as you do in Video. Now trying to load it. But it still trying to load all table. How can I solve this problem?
@@onurturna404 Couple of suggestions
1. Do not apply any merges, it slows the query down.
2. Pull only relevant columns from the entire data.
3. You can also use Query Diagnostics to find out what is taking time in the query
Hope this helps