Пікірлер

• 
  @tibibara2 сағат бұрын

  Very useful tutorial, thanks!💯

• 
  @stanTrX15 сағат бұрын

  Didnt even understand the purpose 😊

• 
  @lauphyonКүн бұрын

  How to add EOTG? I couldn't find it. Thank you!

• 
  @TP014563Күн бұрын

  I didn't know that some my work was way ahead of you, but then other methods I didn't know they were possible. This proves the saying "No matter how much you know something there will always be new things to learn about!". Thanks for sharing, really informative! Keep them coming.

• 
  @therevenant211Күн бұрын

  Will these methods work if the issue is that other users cannot refresh the file? For example, I have 3 different data sources driving queries in a tool that I need to make usable for my employees. But none of them can refresh the file because the filepath has my userid in the filepath. So would I need to create a named cell that contains a filepath that adjusts that userid section of the path depending on who is doing the refreshing, or am I overthinking it?

• 
  @arbazahmad7177Күн бұрын

  Excellent....🎉 thanks for sharing

• 
  @ExcelOffTheGridКүн бұрын

  My pleasure 😊

• 
  @manueljoaoboiaКүн бұрын

  Great and simple content explanation! I need help on 3 tweaks I didn't find (yet...) a workaround, if you can pinpoint in the right direction: 1. Can I have a the table to process the list of PDF to be created within the same template Excel file 8diferente sheet, obviously)? My table list for PDF is constructed with formulas dependent on the data from a static Summary Sheet "reading" with formulas from a PowerQuery data table sheet and also directly from another summary sheet dynamic table); 2. Can the data to process the list update a selection cell in a dynamic table and force its sync? 3. The TEMPLATE to PDF is based on a dynamic table so the print range is not fixed... is it possible to calculate and insert in your solution the calculation and definition of a different print range for each PDF to be created? Thanks in advance for any help!

• 
  @waitplanwp4129Күн бұрын

  big hugs, love you ❤

• 
  @ExcelOffTheGridКүн бұрын

  Wow! Thanks. 😁

• 
  @v2pumo8172 күн бұрын

  Nice combination of wonderfull functions . Thanks a lot

• 
  @patrickschardt77242 күн бұрын

  Wow this is incredible That row/ index: trick is amazing

• 
  @ExcelOffTheGrid2 күн бұрын

  Thanks - I love INDEX it's so powerful. That method is useful in lots of places, so I'm sure you can use it in other scenarios.

• 
  @Dexter101x2 күн бұрын

  Any python tutorials in excel? I updated excel and now it has a preview of python in excel

• 
  @ExcelOffTheGrid2 күн бұрын

  Not from me at the moment, but maybe at a future point.

• 
  @eduardomunoz27642 күн бұрын

  Brillant explanation, Mark. Thank you very much.

• 
  @ExcelOffTheGrid2 күн бұрын

  Thank you. I'm glad it was useful.

• 
  @LegendScroller2 күн бұрын

  I liked #4 that one was my 💡 moment.

• 
  @ExcelOffTheGrid2 күн бұрын

  Great stuff - hopefully you can put it to good use.

• 
  @mohsenabdelbaset2 күн бұрын

  Very nice thanks Mark

• 
  @ExcelOffTheGrid2 күн бұрын

  Thank You, I'm Glad you enjoyed it.

• 
  @rebekahhenken58052 күн бұрын

  Why am I not seeing "wsRanArr" in dynamic content in my run script action? I can see "wsRanArr" if I use a compose action.

• 
  @aneeq75312 күн бұрын

  Great Video

• 
  @jerrydellasala76432 күн бұрын

  Very nice!

• 
  @ExcelOffTheGrid2 күн бұрын

  Thanks Jerry 😁

• 
  @IvanCortinas_ES2 күн бұрын

  Brilliant explanation, Mark. Thank you very much for sharing the different options. It is a very interesting topic.

• 
  @ExcelOffTheGrid2 күн бұрын

  Thanks Ivan - hopefully there are some interesting techniques in there that you can apply at some point.

• 
  @roboats56852 күн бұрын

  I love this channel. Well-organized presentation with clear examples and clever solutions.👍

• 
  @ExcelOffTheGrid2 күн бұрын

  Thank you - that's really kind of you to say. 😁

• 
  @dannywood74502 күн бұрын

  Love it Mark. Challenge: I use method 1 to create a mirrored table from an AddIn download. It has a 3 tiered hierarchy which I sort unique filter into horizontal lists with named ranges for dependent validations. Is there a way to do this dynamically from the table without creating filtered lists?

• 
  @ExcelOffTheGrid2 күн бұрын

  Data Validation Lists do not calculate, they only reference. So you need to reference to another object which will calculate sort/unique etc. You can use a Dynamic Array into a cell, then use the spill range of that cell in the DV. Or you can use a Dynamic Array into a named range and use the named range in the DV. Either way, you have to use something else to trigger the calculation, you can't do it directly in the DV.

• 
  @dannywood74502 күн бұрын

  Thanks so much for your response. So kind. Was hoping there some crazy excel indirect magic I didn't know about. Absolutely dig your channel mate.

• 
  @dannywood74502 күн бұрын

  Thanks so much for your response. So kind. Was hoping there some crazy excel indirect magic I didn't know about. Absolutely dig your channel mate.

• 
  @GeertDelmulle3 күн бұрын

  Good stuff, Mark! On the last example I go one step further and actually call the header row “headers” (as a defined name). Then it looks even more like a table ;-)

• 
  @ExcelOffTheGrid2 күн бұрын

  Nice idea. I like it. 👍

• 
  @RichardJones733 күн бұрын

  I wish i had the talent to solve these on my own. Thanks, this has opened up a lot of possibilities for me. Big fan of dynamic arrays and tables and now i can use both simultaneously

• 
  @ExcelOffTheGrid2 күн бұрын

  Thankfully, we don't need to solve these on your own... there is a world of people all sharing ideas. And we all get to learn from each other. 😁

• 
  @stefankirst32343 күн бұрын

  Ingenious ways to think about this problem! And as always extremely well structured. Thanks a lot. I find method #2 particularly interesting - even if Excel has to sweat a little...

• 
  @ExcelOffTheGrid2 күн бұрын

  Thanks 😁. Method #2 is good, but if your workbook becomes slow, that it is the bit I would look to change.

• 
  @sujirpinna3 күн бұрын

  WhoA !! Absolutely Insightful and very demonstrate the power of combining functions (Filter , Search , Slicers in tandem) and making complex tasks much easier

• 
  @twarren36853 күн бұрын

  This is one of the best videos on the Excel filter function I've seen. You covered techniques that will set my spreadsheets apart from others. Thank you!!!🥰

• 
  @angelmaravilla47083 күн бұрын

  Great video Sir!, Just to confirm: is this "Group By" feature, the equivalent to use the SUMMARIZE() DAX expression?

• 
  @DaveIsAtWork-Really3 күн бұрын

  This is exactly what I have been looking for! Very easy to follow. I think I'll stick with Option 1 for now, until I get a better grasp on working with the data model and power query. Old dog learning new tricks and all :)

• 
  @kartikmohan30014 күн бұрын

  Thanks man, super helpful

• 
  @servespirit19914 күн бұрын

  Worked perfectly. Thank you :3

• 
  @Shriraj275 күн бұрын

  Used this in one of the first record lookup in the nested table, that too in PDF file.... Loved the trick

• 
  @SURYADEEPRGOMES5 күн бұрын

  Hello, Amazed with your presentation in Power Query, just want to get acknowledged why two columns are selected while merging Query, utility of such process

• 
  @Bhavik_Khatri6 күн бұрын

  A highly engaging and informative video presentation.

• 
  @kebincui6 күн бұрын

  Awesome👍👍

• 
  @simsong19116 күн бұрын

  Brilliant! By using the "old" macro I had to save the file as macro enabled. Before your awesome trick I was creating text like '=1+1 and then doing search =, replace by =.

• 
  @daleanderson52586 күн бұрын

  That was great, thanks. What a powerful function!

• 
  @PANDHARI_BOSS_CYDA6 күн бұрын

  THX A LOT

• 
  @85MA7 күн бұрын

  Assistance Needed with Power Query Error I have a folder named BankStatPDF that contains one Excel file named ConsolBS and two PDF bank statements named BankStatementMay and BankStatementJune. I transformed the BankStatementJune PDF file using Power Query in the ConsolBS worksheet, and it worked perfectly. However, when I changed the source file from BankStatementJune to BankStatementMay, I encountered the following error: Expression.Error: The key didn't match any rows in the table. Details: Key= Id=Table001 Table=[Table] Note: The PDF file BankStatementJune has 9 pages, so it was converted into 7 tables in Power Query. The PDF file BankStatementMay has only 3 pages. The format and bank statement details are the same for both files, and they are from the same bank. The Power Query editor has created 7 query tables of these 9 pages for BankStatementJune. I appended them into a new query named "Statement," so I now have a total of 8 queries. All of these 7 query tables showing the same error except the "Statement" query, which displays: An error occurred in the ‘Table001 (Page 2)’ query. Expression.Error: The key didn't match any rows in the table. Details: Key= Id=Table001 Table=[Table] Please guide me on how to fix this error. Note: The PDF file BankStatementJune is used to transform data in power query that has 9pages so it converted into 7tables. The PDF file BankStatementMay have only 3pages. rest the format and bank statement are same and of one bank. Thank you for your assistance.

• 
  @ennykraft7 күн бұрын

  You're the GOAT! Thank you! Could you maybe do another video about all the different calcations in GROUPBY and PIVOTBY? I love what ARRAYTOTEXT in combination with SUBSTITUTE does. Maybe examples for LAMDA? Another question: I've noticed that in my version there's an extra option in GROUPBY called field_relationship. Do you know what that one does?

• 
  @westleyempeigne65417 күн бұрын

  Hi Mark, great tips! My fav are trick 3,4,5 and 8! Leaning new things all the time but also need to try and incorporate these into my work!

• 
  @Kuriacs8 күн бұрын

  You are awesome, this was a fascinating technique! Enjoyed watching.

• 
  @90hsilva8 күн бұрын

  Thank you for this very useful video, the way you explain is very easy to understand it.

• 
  @bretogden38388 күн бұрын

  Tip 6 is awesome!

• 
  @mrbartuss18 күн бұрын

  3:25 I did it this way: = Table.AddColumn(#"Replaced Value", "Date", each { Number.From([Date From]) .. Number.From([Date To])}, type date) Which would be better and why?

• 
  @ExcelOffTheGrid8 күн бұрын

  Yes, that will work too. I don’t think it makes much difference which method you use.

• 
  @fernandogames39888 күн бұрын

  Amigo, você me salvou muito! Merece um like/inscrito e trabalho merecido. Venho do Brasil assistindo os seus vídeos 😄

• 
  @ironicdaemon8 күн бұрын

  My tip of the day: MAP() is the same as BYROW() or BYCOL() if only a single column or row is selected respectively.

• 
  @ExcelOffTheGrid8 күн бұрын

  Yes, that is true. But would you say that VLOOKUP is the same as INDEX/MATCH if the lookup value is in the first column? 🤔

• 
  @mytciopinfo8 күн бұрын

  Ned Schneebly, is that you?

• 
  @90hsilva8 күн бұрын

  Great example I have exactly the same issue and didn’t know this way to solve it. Thank you very much

• 
  @barttitulaerexcelbart94009 күн бұрын

  Great explanation Mark! I just hat a second look at Alberto Ferrari and Marco Russo's book: "Analyzing Data with Microsoft Power BI and Power Pivot for Excel". Chapter 5 is about SCD's but now I understand! thanks!

• 
  @krzysztofmmichaluk9 күн бұрын

  super!

• 
  @SmallingYari9 күн бұрын

  Thank you! This is the best teaching about E O month function i have ever watched ❤