Make Queries Unbreakable : 5 Powerful Hacks | Power Query Tricks

แชร์
ฝัง
  • เผยแพร่เมื่อ 27 ก.ค. 2023
  • Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
    In this video, we will learn five awesome hacks and techniques to make your Excel queries unbreakable. These tips will help you avoid common errors and ensure that your queries are accurate and reliable. Watch the video to learn various hacks, such as expanding columns dynamically, removing change type errors, ignoring missing fields, and capturing errors. These tricks will make your queries more robust and secure.
    ===== Related Videos =====
    Ignoring Case Sensitivity in Power Query - • Ignoring Case Sensitiv...
    Remove 'Change Type' Errors in Power Query - • Remove 'Change Type' E...
    Table.TransformColumns in Power Query - • Table.TransformColumns...
    Missing Field Ignore | How NOT to break your Queries - • Missing Field Ignore |...
    #ExcelQueries #powerquery #advanceexcel
    ===== ONLINE 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...
    ===== LINKS 🔗 =====
    Blog 📰 - www.goodly.co.in/blog/
    Corporate Training 👨‍🏫 - www.goodly.co.in/training/
    Need my help on a Project 💻- www.goodly.co.in/consulting/
    Download File ⬇️ - goodly.co.in/crackproof-your-...
    ===== CONTACT 🌐 =====
    Twitter - / chandeep2786
    LinkedIn - / chandeepchhabra
    Email - goodly.wordpress@gmail.com
    ===== WHO AM I? =====
    A lot of people think that my name is Goodly, it's NOT ;)
    My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
    Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!
  • วิทยาศาสตร์และเทคโนโลยี

ความคิดเห็น • 68

  • @GoodlyChandeep
    @GoodlyChandeep  10 หลายเดือนก่อน +1

    Download File ⬇ - goodly.co.in/crackproof-your-queries-hacks-to-make-your-query-unbreakable

  • @Tattysnuc
    @Tattysnuc 10 หลายเดือนก่อน +3

    "Just eat the mango and you're going to enjoy". Love it. Top tips. Thank you!

  • @alexkim7270
    @alexkim7270 10 หลายเดือนก่อน +2

    Whoa that error message trick is insanely good. Thanks for sharing!

  • @chrism9037
    @chrism9037 10 หลายเดือนก่อน +1

    The columns headings hack was brilliant, thanks Chandeep!

  • @DinoDelight
    @DinoDelight หลายเดือนก่อน

    I love that errors one, the amount of times I have to say to colleagues..."you got dodgy data"

  • @unnikrishnansanthosh
    @unnikrishnansanthosh 10 หลายเดือนก่อน

    excellent one, thank you for sharing

  • @baskis69
    @baskis69 10 หลายเดือนก่อน

    Amazing tricks¡¡¡¡ Thanks for sharing

  • @excel-in-g
    @excel-in-g 10 หลายเดือนก่อน

    As always.... Superb stuff by Chandeep!

  • @dilipinamdarpatil6301
    @dilipinamdarpatil6301 10 หลายเดือนก่อน

    Very useful..... Thanks 🙏🏼..

  • @mohammedelsakally540
    @mohammedelsakally540 10 หลายเดือนก่อน

    Thank you Chandeep for this informative video ...

  • @advrohitowhal9794
    @advrohitowhal9794 8 หลายเดือนก่อน

    Error Reason & Message Trick was Awesome!

  • @mirrrvelll5164
    @mirrrvelll5164 6 หลายเดือนก่อน

    Pure gold of tricks. thanks man

  • @YvesAustin
    @YvesAustin 3 หลายเดือนก่อน

    Found this gem in my feed today. I’ll have to bookmark it! Awesome tricks. Thank you 🙏

  • @udayteja6595
    @udayteja6595 9 หลายเดือนก่อน

    Expanding list of tables dynamically is cool...

  • @arbazahmad7177
    @arbazahmad7177 10 หลายเดือนก่อน

    Excellent... Hack 1 case insensitive is great 👍

  • @telclissonperes
    @telclissonperes 10 หลายเดือนก่อน

    Amazing!

  • @DeronHuskey
    @DeronHuskey 10 หลายเดือนก่อน

    Good stuff, especially the column expansion/transformation. I've got your ChangeTypeNoError video in my saved list. That looks like it could provide a LOT of utility. Thanks.

  • @prashantsuthar7
    @prashantsuthar7 10 หลายเดือนก่อน

    It works for me thanks

  • @sanumpzha
    @sanumpzha 10 หลายเดือนก่อน

    This is cool stuff, loved it😍

  • @karinamatvejeva3164
    @karinamatvejeva3164 10 หลายเดือนก่อน

    🔥🔥🔥 I like how You explain, thank You!

  • @joelabacherli1310
    @joelabacherli1310 10 หลายเดือนก่อน

    Another brilliant video. Thank you so much for sharing this knowledge.

    • @GoodlyChandeep
      @GoodlyChandeep  10 หลายเดือนก่อน

      Glad it was helpful :)

  • @IamLillerz
    @IamLillerz หลายเดือนก่อน

    Thank you so much! 👍

  • @s1ngularityxd64
    @s1ngularityxd64 10 หลายเดือนก่อน +1

    really cool stuff, love the error handling part😊

  • @jerrydellasala7643
    @jerrydellasala7643 10 หลายเดือนก่อน +1

    Great video as always. I notice your function is documented. I'd love to see a video on how to add that to a custom function. Also, how to convert a query into a function where the function becomes a new query linked to the original query so that changes made to the original query get passed to the function automatically the way it happens when using Get Files from Folder.

  • @taizoondean689
    @taizoondean689 10 หลายเดือนก่อน

    Superb 😊

  • @sayi77
    @sayi77 10 หลายเดือนก่อน

    What an amazing man you are.

    • @GoodlyChandeep
      @GoodlyChandeep  10 หลายเดือนก่อน

      What an amazing comment 😍

  • @alexrosen8762
    @alexrosen8762 10 หลายเดือนก่อน

    Fantastic tutorial, especially the capturing errors part

  • @RichardJones73
    @RichardJones73 10 หลายเดือนก่อน

    Excellent video. It really helps to identify errors this way, saves hours of searching. And for the dynamic loading of columns is another game changer

    • @GoodlyChandeep
      @GoodlyChandeep  10 หลายเดือนก่อน +1

      Thanks Richard :)

  • @malchicken
    @malchicken 10 หลายเดือนก่อน

    Excellent summary, thank you 😊. I most enjoyed seeing how to log errors. 👍🏽

  • @Chillman666
    @Chillman666 10 หลายเดือนก่อน

    Cool stuff as always! Personally I like receiving error messages that certain column wasn't found because I would like to know right away if an essential column was renamed or removed from the reports

  • @ymohamed3154
    @ymohamed3154 10 หลายเดือนก่อน

    Thanks❤

  • @emilmubarakshin49
    @emilmubarakshin49 10 หลายเดือนก่อน

    This is bananas - especially the trick #5

  • @christianmarpert3844
    @christianmarpert3844 10 หลายเดือนก่อน

    really nice video! my favourite is also the dynamic column names one. do you know if you can create your own library bundling all the custom function, to easily include it into each project instead of copiing over? a while ago i was researching on that, but couldn't find anything...

  • @oscarmendez-roca9181
    @oscarmendez-roca9181 10 หลายเดือนก่อน

    Awesome set of tricks, thanks a lot Chandeep. I have a question: how do you can replace the functionality of the "??" operator in old versions of Power Query?

  • @tonydreisenstock
    @tonydreisenstock 10 หลายเดือนก่อน

    I have learnt so many really clever automations from you. Your videos are clear incisive and extremely effective. Thank you so so much

    • @GoodlyChandeep
      @GoodlyChandeep  10 หลายเดือนก่อน

      You're very welcome Tony :)

  • @damionc
    @damionc 10 หลายเดือนก่อน

    You never cease to amaze. I was already doing number 1 but number 2 is a very nice trick. #3 is even better. I am writing this before I even finish watching.

  • @Dan_De_Man
    @Dan_De_Man 10 หลายเดือนก่อน

    Nice tips!
    When adding the index step, shouldnt you change it to start from 2(or wherever your data starts) so that it will actually show the row number of the error in source file.

  • @sandipansarkar9211
    @sandipansarkar9211 6 หลายเดือนก่อน

    Finished watching

  • @mrrafael161
    @mrrafael161 10 หลายเดือนก่อน +3

    Hello everyone,
    With reference to the case 2. Expanding Coumns Dynamically
    Wouldn't it be easier and more efficient to capture the names of the columns by creating the below step ( after Grouped Rows ):
    = List.Union( List.Transform( #"Grouped Rows"[ All] , each Table.ColumnNames(_) ) )
    ?

  • @b0rme
    @b0rme 10 หลายเดือนก่อน +1

    Thanks for the video, and I like the tips. However I suppose queries 'breaking' and Power Query being sensitive is actually a good thing. It ensures that data sources and the structure of the data remains consistent. Nothing ever just 'breaks': there is always a good reason for why the query stops working.

    • @bommy3272
      @bommy3272 10 หลายเดือนก่อน

      To be fair it could entirely depend on your purpose.
      The reason I say that is because for I use power query to track employees workload. How many items they actioned and time taken to complete.
      Due to the majority of information being manually inputted by end users, there is always someone that enters a weird value or made a change, they didn't know they made. End users just generally know "Thing did not work" Even though they are excel workbook is managed by the manager, there is always someone that "breaks" something.
      Being able to have these accounted for while still gathering the correct data is beautiful.

  • @KhajonsakLun
    @KhajonsakLun 10 หลายเดือนก่อน

    Thank you very much for useful tricks. \^^/

    • @GoodlyChandeep
      @GoodlyChandeep  10 หลายเดือนก่อน

      You're very welcome!

  • @prasaddongare5779
    @prasaddongare5779 10 หลายเดือนก่อน

    Hi, how to select chart's x-axis, y-axis, legends and values? how to differ them? what are the basic criteria's for choosing them?

  • @mohitshyngle8200
    @mohitshyngle8200 6 หลายเดือนก่อน

    While Expanding Column Dynamically, Post the expanding, I wish to add a coulmn of SUM, Sum of Collist, it gives error, Please help

  • @sujit3375
    @sujit3375 10 หลายเดือนก่อน

    I am highly interested in joining your course
    But one question goodly
    Is that power query courses update with such awesome content time to time
    Or it remains static as content list given

  • @surajbhardwaj428
    @surajbhardwaj428 9 หลายเดือนก่อน

    Hi Brother...✋ But how can I know the which formula will use for which purpose ...

  • @Rice0987
    @Rice0987 10 หลายเดือนก่อน +1

    3:57 you've added bubble sound to your transition.😂👍

  • @mattnyman9933
    @mattnyman9933 9 วันที่ผ่านมา

    Is there a way to go from type "Any" to type "Date" skipping the type "DateTime" when there is a time in the data?

  • @txreal2
    @txreal2 9 หลายเดือนก่อน

    ChangedTypeNoErrors 👍

  • @shoaibrehman9988
    @shoaibrehman9988 10 หลายเดือนก่อน

    Hi Chandeep could you pls assist me.

    (POWER QUERY FROM OFFICE 365)Access Permissions Required to open queries from power query connections.
    Can i protect my queries to view

  • @devo098
    @devo098 9 หลายเดือนก่อน

    When all the data is flat and the same type, instead of manually changing each column type, you can unpivot the data from the date column. This gives the ability to. Change all the values to whatever type you need. Then pivot again. No hard coded names.

  • @RakeshSaha1705
    @RakeshSaha1705 10 หลายเดือนก่อน

    I need one help with s curve. I am unable to restrict the actual line chart to a certain date. The columns are date, product, amount, planned value, actual value. Product column has different products. Date column contains date till dec 23. Actual value column contains value till July 23. Planning value contains value till dec 23. Using running total measure for cumulative planning value is working fine. But the same measure for cumulative actual is showing value even after July 23. If possible make one video to restrict cumulative actual to a certain date.

  • @sambazonatl
    @sambazonatl 10 หลายเดือนก่อน

    “Just eat the mango.” Brilliant.

  • @Rice0987
    @Rice0987 10 หลายเดือนก่อน

    2:34 i just remove that hard coded text, leave column name only.🤔

  • @minadev1
    @minadev1 3 หลายเดือนก่อน

    All went too quick for me, who I can get my head around this topic

  • @vikashparihar8366
    @vikashparihar8366 10 หลายเดือนก่อน

    Hi
    I have 1 issue with POWER BI, (basically related to managing one to many relationship with dates, user relationship dax is not a scalable option for me here.) pls can you share mail id or any other place where I can describe the problem i am getting with my project.