Hi Mark. Great exercise! I like to try and solve before watching. To return all records if the filters are blank, I used the Advanced Editor to modify the M code with an an IF THEN ELSE construct on each existing filter as follows: #"Filtered Rows" = if AccountFilter null then Table.SelectRows(#"Promoted Headers", each ([Account] = AccountFilter)) else Table.SelectRows(#"Promoted Headers", each true), #"Filtered Rows1" = if CategoryFilter null then Table.SelectRows(#"Filtered Rows", each ([Category] = CategoryFilter)) else Table.SelectRows(#"Filtered Rows", each true) It eliminates a few steps. Just another way to get there. Thanks for the video! Learned a lot :)) Thumbs up!!
Very cool! I can't think of a situation where this would be applicable, but you never know! Unfortunately, Excel 365 doesn't behave and won't pull in a single cell as a named range through the Interface. I was able to get it using From Workbook, but that's a mess and would require two more source file corrections (BTW, thanks for providing all 3 files!). In Excel 365 it's easier to just create a new Blank query and start it with: = Excel.CurrentWorkbook(){[Name="AccountFilter"]}[Content] and proceed as shown here. Being a bit of a stickler for wanting as few steps as possible, rather than using a separate Custom function to filter the rows, I replaced the first Filter with = if AccountFilter "" then Table.SelectRows(#"Changed Type", each ([Account] = AccountFilter )) else #"Changed Type" but that was totally inspired by your technique which is admittedly clearer for the demonstration. You did miss mentioning to go to the resultant Table Properties and uncheck Adjust Column Width. I miss it all the time too, but you see it really quickly! Anyway, nice lesson!
Hi Jerry - Thanks. I can appreciate your position of trying to reduce steps. I try to keep steps as "standard" as possible so if I need to delete/add new steps it's faster to do. I'm surprised that Excel 365 doesn't work for you. As I create the video on Excel 365. Very strange 🤔 That's a good point about the Table Properties. I might do that as another video, as there might be quite a few people who don't know about that.
Coming in super late but this was exactly what I needed. Much appreciated!
So wonderful properties of PQ and Data Validation combination!! Amazing.
Thank you
Thanks Emre - glad you found it useful.
This is what I was looking for. Simple and clear explanetion of each step. 👍
Great news. Glad I could help.
hello! nagyon jó videóid vannak! sok segítség ez!
Köszönöm, örülök, hogy hasznosnak találtad!
Mark, I didn't know I needed this and now I wonder how I managed without it. Great technique. Thanks for posting
Lol!! Thanks Grainne. Enjoy implementing it 😀
Hi Mark. Great exercise! I like to try and solve before watching. To return all records if the filters are blank, I used the Advanced Editor to modify the M code with an an IF THEN ELSE construct on each existing filter as follows:
#"Filtered Rows" = if AccountFilter null then Table.SelectRows(#"Promoted Headers", each ([Account] = AccountFilter)) else Table.SelectRows(#"Promoted Headers", each true),
#"Filtered Rows1" = if CategoryFilter null then Table.SelectRows(#"Filtered Rows", each ([Category] = CategoryFilter)) else Table.SelectRows(#"Filtered Rows", each true)
It eliminates a few steps. Just another way to get there. Thanks for the video! Learned a lot :)) Thumbs up!!
Good work 👍
I like that you try to solve the problem before watching the video.
Wonderful! What I always wanted to do, but never could. Till now.😀
Well... you can now. Great news!
Thanks Mark 👍👍👍👍
You're welcome, thanks for watching.
Brilliant 😊
Thanks 😀
Thank you!!!!
You're welcome!
Very cool! I can't think of a situation where this would be applicable, but you never know!
Unfortunately, Excel 365 doesn't behave and won't pull in a single cell as a named range through the Interface. I was able to get it using From Workbook, but that's a mess and would require two more source file corrections (BTW, thanks for providing all 3 files!). In Excel 365 it's easier to just create a new Blank query and start it with:
= Excel.CurrentWorkbook(){[Name="AccountFilter"]}[Content]
and proceed as shown here.
Being a bit of a stickler for wanting as few steps as possible, rather than using a separate Custom function to filter the rows, I replaced the first Filter with
= if AccountFilter "" then Table.SelectRows(#"Changed Type", each ([Account] = AccountFilter )) else #"Changed Type"
but that was totally inspired by your technique which is admittedly clearer for the demonstration.
You did miss mentioning to go to the resultant Table Properties and uncheck Adjust Column Width. I miss it all the time too, but you see it really quickly!
Anyway, nice lesson!
Hi Jerry - Thanks. I can appreciate your position of trying to reduce steps. I try to keep steps as "standard" as possible so if I need to delete/add new steps it's faster to do.
I'm surprised that Excel 365 doesn't work for you. As I create the video on Excel 365. Very strange 🤔
That's a good point about the Table Properties. I might do that as another video, as there might be quite a few people who don't know about that.
i always wonder how one can think of this themself... crazy stuff
Problems arise and I try to find solutions... that's pretty much it.
Thanks for sharing
I problem. Thanks Frank.
Hello,. Need help on this scenario how to split the below 1 values to the columns by using formulas..
#opportunities=1#Defect=1