5 Surprising Power Query Tricks You Need to Know!

แชร์
ฝัง
  • เผยแพร่เมื่อ 14 มิ.ย. 2024
  • Check out our newly launched M Language course ↗️ -goodly.co.in/learn-m-powerquery/
    Introducing 5 incredibly handy M language tips that can accelerate your daily tasks in Power Query!
    #powerquery #Mlanguage #dataanalysis
    ===== 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/5-surprising-pow...
    ===== 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!
  • วิทยาศาสตร์และเทคโนโลยี

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

  • @GoodlyChandeep
    @GoodlyChandeep  7 หลายเดือนก่อน +2

    Download the File ⬇ - goodly.co.in/5-surprising-power-query-tricks-need-know
    Check out the M Language course ↗-- goodly.co.in/learn-m-powerquery/

  • @michaelt312
    @michaelt312 7 หลายเดือนก่อน +5

    Absolutely mind blowing. Honestly knew none of these. You've quickly become my favorite Excel/PQ/BI channel. Honestly probably my second favorite TH-cam channel in general. Sorry, I am a big Nebraska Cornhusker fan, so a channel about them will always be top.

  • @masterjanglez
    @masterjanglez 7 หลายเดือนก่อน +6

    Love the content and what you provide for all us out here in the "dataverse". Really enjoyed that you kept the power outage in, reminding us all that content creators have technical difficulties too. Nice job all around 😁

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

    Brilliant! Thank you for all the knowledge!!!!

  • @zahoorsarbandi2982
    @zahoorsarbandi2982 7 หลายเดือนก่อน +1

    absolutely outstanding explanation

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

    Craziest most amazing power query guy. Suuuuper valuable, so looking forward running through your courses! Thank you!

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

    Vraiment super vos vidéos ! 👍👌

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

    Love the tutorial, thanks for the tips!

  • @hariprasad289
    @hariprasad289 7 หลายเดือนก่อน +1

    Awesome Sir. Thank you very much for sharing such great knowledge

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

    I ALWAYS learn something good from your videos!!!

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

    I just used trick nº 3... Thanks for sharing... PQ it's magic...I love it ¡¡¡¡¡¡¡¡

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

    The power interruption was so funny!! So Real! So Organic!!

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

    Thank you for your content. It’s fantastic to see the possibilities of m-code. 😊
    4:54 one can also use “#(lf)” in order to split in rows.
    To aggregate numbers I use the following code
    {"S_Qty", (r) => Text.Combine(List.Transform(r[Qty], each Text.From(_)),"#(lf)"), type text },

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

    Thank you for this important information it is really hepful

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

    As always, Amazing!!

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

    Million thanks

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

    Very nice! Speaking of custom functions, I'd love to see a video on how to properly document a custom function - especially so that the function still works with intelitype!

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

    7:21 Wow, that is a really awesome trick! Thank you, Chandeep!!! You are the best at Power Query!!!

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

    Fantastic!!!!!!!!

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

    I’ve watched this a few times since you uploaded it and I never *not* laugh at your reaction to the lights going out.
    Great video as always.

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

    Thank you, mr. nike boss!🤗

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

    Wonderfull!!!

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

    awesome!

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

    Trick number 3 is awesome 👍

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

    Wow treasure tricks

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

    excellent

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

    That trick with using records to make multiple columns is so clutch!!!

  • @pk231
    @pk231 7 หลายเดือนก่อน +2

    By using the second trick we can bypass one of the concepts shared in your video about a list where you used a list to automatically remove errors to null.
    In that scenario you tranposed the table and then converted to list by saying Table.ToColumns but we could use Table.ToRows to do that I guess
    Thanks for your tricks

  • @m.bouguerra
    @m.bouguerra 6 หลายเดือนก่อน

    hi and big thank you for all these tips...
    I have a little problem with the first tip, I did exactly like you but the line break does not work in my editor .. possible that there is a setting in the power query parameters?
    thank you for answering me

  • @allabout1135
    @allabout1135 7 หลายเดือนก่อน +1

    Could You make series about data modelling? Its so many videos about Power BI Tool it self but so less good videos with deep dive in data modelling and DB theory.

  • @Tattysnuc
    @Tattysnuc 7 หลายเดือนก่อน +1

    great recovery from the power cut :) Great content as always.

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

      Yeah.. It was only 2 hours in a snap of millisecond.

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

    Thanks. Also, we can use Table.Column to create a combined list ?

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

    Amazing thanks 🎉
    At the moment im struggling with one task, maybe you can support me how to do.
    Task:
    Which two items are the most frequently used item. Consider the transaction code.
    Table 1:
    Column A: transaction
    Column B: item
    Column C: quantity
    Table 2:
    Column A: all possible combination of the items
    Column B: (result) how often the combination has been used.

  • @user-lm5wb8vi1x
    @user-lm5wb8vi1x 6 หลายเดือนก่อน

    finished watching

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

    Just to add loved the custom messages at the end. 😀😀

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

    Awesome! But where did the light go? :D

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

    Mind-blowing tricks !!! I can really be honest and say that I don't regret any cent getting your M-Language in PQ (it is not like promo but the truth from my side). That is/was a jewel, a couple of these things are already there (like this with "is number", or transpose part).
    Additionally, is there any video or something where you provide shortcuts in PQ, like operating in advanced editor, moving, shifting etc? So everything that is useful.

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

      th-cam.com/video/Hh1A2oiyenM/w-d-xo.htmlsi=fW3tJa59F6M3r6iE

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

    Is there a way to create a dynamic number of power query tables based on the number of rows in a parameter table? I have an "Admin" sheet that contains a parameter table with 1 column "EntityName". and a fact table (with EntityName) as one of the fields. The user will input the entity names into the parameter table and click generate sheets. Using VBA, a sheet will be created for each row in the parameter table. I want to be able to output a table in each of the generated sheets that filters the fact table by the entity for each sheet. For example if 2 sheets were created (Entity A and Entity B), I would want to output a table on the Entity A sheet that is a filtered fact table for only EntityName = "Entity A". Same for Entity B. Any ideas if this is possible/if you see a better solution using only PowerQuery (rather than VBA)??

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

    This tutorial has actually told me that i am not effectively utilizing power query

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

    On the first trick using the "Shift-Enter" to create multi-row output: this does not seem to translate to in-cell carriage returns in Excel like the "Alt-Enter" in Excel. Did I miss something? Also, I would wrap a "List.Sort()" around the "List.Distinct()" to create sorted output for the "List.Combine()". Thanks for all you do. Great job.

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

      I have the same problem, it might be the difference among excel versions. Would Chandeep help look at this particular problem?

  • @y1.5
    @y1.5 7 หลายเดือนก่อน

    TH-cam algorithm working properly 👍🤝

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

    Hi Goodly, thank you for the amazing content as usual, I have a question regarding trick number 1 @4:40 when you used Shift + Enter, each of the products shows in a different row, however when you load the data they are still in the same cell and they become concatenated, thoughts on that please, thank you again

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

      Hey Goodly, hi again, found the solution for my answer, when the table is load , i just had to go to column Products and on the home tab of excel, just hit the warp text. 😉

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

    I used the shift enter today for the first time - in DAX in power pivot!! - because it does not recognize unichar/char(10)

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

    Hello sir, I like your videos and I have learned so much from ur videos, recently I have shifted to Dubai because of my Excel skills which I have been able to develop from you, can we meet sir if possible 😊😊

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

    Are your coursed available on udemy?

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

    Sir, plz make video for below.
    If
    1 table has 9 column,
    2 table has 10
    and 3 table has 8 .
    Then how can we make equal all column for all table.. like we want to insert dummy column for missing column in tables and make equal like 10 column for each table.

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

    You video is full of awesome M code info. My M code experience is basic and after watching this video, I have an open mind on the possibilities. Thank you! How do you mark the screen, red pen while you're recording? I want to do this on my Power BI videos for enablement sessions.

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

      Personally I use ZoomIt which is a WIndows SysInternals Tool. I think Leila G did a good video on SysInternals Tools.
      I've just realised I can draw boxes and lines with Ctrl & Shift respectively! Yay! :D

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

      Zoomit

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

    Hi chandeep, I've learnt many concepts from you. Thank you so much.
    In this video, you have said to create multiple columns with record, may I know any performance improvement instead of AddColumn. Please let me know.

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

      I haven't seen any performance degradation by creating multiple columns using records unless you're doing something funky. It make the query shorter and doesn't lose or gain on performance.

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

      Then really I would love to do this way, as it so elegant and clean way.
      Some times I use List.Accumulate to avoid multiple columns, if my column additions has a pattern, with this new insight, I can do better with a mix of both. Thanks for your response.

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

    I should say This guy....This guy is wooooow

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

    12:36 Why are they blurred out?

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

    oh man !

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

    Very useful content. Thank you @GoodlyChandeep

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

    How this channel is underrated. It's an amazing channel to learn. Thank a lot @goodlyChandeep

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

    Did not know the 'is', I've often used Vaue.Type as a condition in an if statement,
    is may be simpler. Re records, is there any way to transform a column by the value in another column.
    so; Table.TransformColumns(
    Table.FromRecords(
    { [A = 3 , B = 2 ], [A = 2 , B = 3] , [A = 4, B = 5 ] }
    ), { "B", each _ * [A] } ) I know this does not work but illustrates what I've been trying to achieve for ages.
    The best I've got involved torows / join / totable etc.

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

      Unfortunately referencing other columns is not possible in Table.TransformColumns but there is workaround. I've got to make a video on that too!