I Bet You Don't Know All These Power Query Tricks

แชร์
ฝัง
  • เผยแพร่เมื่อ 27 พ.ย. 2024

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

  • @randomguy-jo1vq
    @randomguy-jo1vq ปีที่แล้ว +18

    0:19 Promote Headers without step
    1:28 Extract any intermediary step
    4:00 Meta keyword
    5:09 dynamic expansion of the tables
    7:07 projections
    8:18 sections keyword
    9:18 is keyword
    10:21 & Operator

  • @hashi856
    @hashi856 ปีที่แล้ว +5

    The meta trick is awesome. I've been having to create a copy of the query any time I want to convert it into a list of records.

  • @pianoquatrevingtquinze9220
    @pianoquatrevingtquinze9220 ปีที่แล้ว +1

    I've been using power query with the GUI for a few years now. Since I have been following you videos, I have made great progress. Thank you very much.
    Also, I want to tell you that I love your style, it's a pleasure to follow you. Thank you!

  • @lynxwomancat
    @lynxwomancat ปีที่แล้ว +3

    Love the promoted header trick from null to true! Thank you so much for sharing your vast PQ knowledge with us!

  • @jake967
    @jake967 ปีที่แล้ว +3

    This channel is outstanding. Thanks for the efficient demos!

  • @sachin.tandon
    @sachin.tandon ปีที่แล้ว +1

    Very slickly and succintly presented. I also like the video clips of the brand and other areas that make the tutorial easier to follow. Makes it look very professional!

  • @jeromeastier462
    @jeromeastier462 ปีที่แล้ว +4

    Love the error! Seriously I might have known the first one, but the one that I really dig is the dynamic expand data.

  • @thebhaskarjoshi
    @thebhaskarjoshi ปีที่แล้ว +9

    Wow... Amazing tricks
    One Suggestion: Try to add time stamp in the description of each video. It'll help user to refer back any one point quickly for revision purpose later in time.

  • @markusleucht6603
    @markusleucht6603 ปีที่แล้ว +2

    WOW!
    Occult Knowledge from the initiated. Worth every second.
    Thanks so much for your hard work, Chandeep!

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว +1

      Wow occult.. heard that beyond the textbook after a long long time.
      Cheers and Thanks

    • @AK-ij9il
      @AK-ij9il ปีที่แล้ว

      @@GoodlyChandeep my power BI project data is unstructured, unable to transform correctly by using power query, what will I do?

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

    I appreciate #7 the & join of tables; gives PQY a Union query if tables are prepared with Exact same header names. Because of these criteria, I was wondering if you have a trick to pick only certain columns by a list of numbers and to order desired columns in any preferred order ( 170, 2, 3, 4, 164, 15, 10) as column # listed in order desired. For complexity assume the #(cr) needs to be removed from the header row. I currently have about 9 Pwr Qry to do most of the work, but I have a second table that needs to update data rows from another source. That's why I like The AMPERSAND #7 tip.

  • @flyingHubby
    @flyingHubby ปีที่แล้ว +2

    Meta is awesome. Keep all the code, that creates multiple output objects, together in one place and refer to it from the outside. Great structure.

  • @jeromeastier462
    @jeromeastier462 ปีที่แล้ว

    Just used extract any steps + Dynamic Expand. Awsome!

  • @zahoorsarbandi2982
    @zahoorsarbandi2982 ปีที่แล้ว

    All tricks are amazing and very useful. Thanks for sharing.

  • @vinothe6707
    @vinothe6707 ปีที่แล้ว +1

    All new tricks I learned today is pretty awesome....
    Thank you

  • @rickychen1086
    @rickychen1086 ปีที่แล้ว

    great video as always Chandeep! Metadata one is good, did not know it before. for dynamically expanding table, is there a way to do so to expand all the csv files within a folder. using Power Query UI to do that will automatically invoke a custom function and it is a bit messy.

  • @girishsawant8263
    @girishsawant8263 ปีที่แล้ว

    Thank you very much for sharing such valuable knowledge.

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

    After watching this video, I have gained a better understanding of meta and is keywords. These tricks are amazing!

  • @barotkaushik
    @barotkaushik ปีที่แล้ว +3

    You can have multiple metadata with the following syntax
    meta [first = Source,second=FilteredData]

  • @teaking1981
    @teaking1981 ปีที่แล้ว +1

    I would go for the QOL ones, projection, "IS", and "&" they make coding easier to read and simplify it.

  • @PavanSPadiyala
    @PavanSPadiyala ปีที่แล้ว

    Awesome tricks. thanks for your demo excellent

  • @martyc5674
    @martyc5674 ปีที่แล้ว

    Brilliant stuff as always Chandeep- Voted BTW 👍

  • @vinhwizard
    @vinhwizard ปีที่แล้ว

    Very useful. Thank you so much

  • @gdecome
    @gdecome ปีที่แล้ว

    Really cool tricks ... thanks for sharing

  • @ashleehuff3710
    @ashleehuff3710 ปีที่แล้ว

    Very clever way to get viewers to subscribe to your channel, I laughed out loud... and then subscribed.

  • @raimundojs9547
    @raimundojs9547 ปีที่แล้ว

    Simply brilliant!!! "Expression.Error: WTF: This viewer is not Subscribed to your Channel" > now that would be a critical and unforgiven error!

  • @advrohitowhal9794
    @advrohitowhal9794 ปีที่แล้ว

    Amazing....Thanks!!!!!!

  • @Rice0987
    @Rice0987 ปีที่แล้ว

    Omaga!😲
    This is just divine!👏😓
    Thank you power monster!😆🤗

  • @KirillBezzubkine
    @KirillBezzubkine ปีที่แล้ว

    5:56 - maaaaan that was the best PQ trick evaaaaa

  • @jerrydellasala7643
    @jerrydellasala7643 ปีที่แล้ว

    Wow, one of THE best PQ Tricks video I've seen! Hey, I downloaded your file and had to correct the data source in the Tricks Workbook. However, under Data -> Get Data -> Data Source Settings, the source was set to your folder, and the [Change Source] button was disabled. I've run into this often. Is there a way to fix that?
    Thanks for a REALLY GREAT video!

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว

      If you edit the Excel.Workbook function, it gets disabled. I think creating a parameter would be a good way of solving this.

  • @gauravthosar593
    @gauravthosar593 ปีที่แล้ว

    Awesome tricks, all are most wanted for me

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

    Fell in love with the "error" advertisement to your channel. XD

  • @danishnawaz7504
    @danishnawaz7504 ปีที่แล้ว

    Amazing..
    But Expedite the Video series.
    On Multiple PQ Functions.. Like as Tables As well as List Functions....

  • @dannyshort2305
    @dannyshort2305 ปีที่แล้ว +2

    5:57 😂 brilliant

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว +1

      I created a custom subscribe error. 😁😁

  • @arungupta8444
    @arungupta8444 ปีที่แล้ว

    Hi Sir, if there is a set of different Excel files and there are steps applied to them, like merge, combine and group, and there is an update in one/many Excel files in terms of values and it is not possible to update each and every column value, in this case how can we replace the Excel file/files (keeping the same file name) so that the all the steps can/will be applied as it is after file replacement

  • @enocharthur4322
    @enocharthur4322 ปีที่แล้ว

    Wow wow wow this is amazing

  • @kennethstephani692
    @kennethstephani692 ปีที่แล้ว

    Great video!

  • @reanalytics1863
    @reanalytics1863 ปีที่แล้ว

    You are wonderful

  • @MS-sb3gv
    @MS-sb3gv ปีที่แล้ว

    That “is” trick will be very useful. Can’t believe I haven’t used it before

  • @GeertDelmulle
    @GeertDelmulle ปีที่แล้ว

    Unknown to me were: meta, is and the fact that that other thing is called “projection” (I just thought is was a way to extract one or more columns as a table, which it is). Several of these tricks you’ve shown before.
    As new trick I like that “is” trick.

  • @bulbulahmed3098
    @bulbulahmed3098 ปีที่แล้ว

    Great❤

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

    Finished watching

  • @BrainyBrunetteBarbie
    @BrainyBrunetteBarbie ปีที่แล้ว

    Great video!
    Also, that error message was hilarious.

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

    Awesome

  • @silverfunnel6819
    @silverfunnel6819 ปีที่แล้ว

    5:57 👍

  • @nigilv.d4237
    @nigilv.d4237 ปีที่แล้ว

    Sir these all things on ur online course as well right (powerquery)

  • @mahathmasadineni2884
    @mahathmasadineni2884 ปีที่แล้ว

    Hi chandeep as usual awesome.
    If possible could please do a vedio on how to fix PQ errors when the data structure is changed. Like missing of columns or columns name changes. Because of hardcoding of column of column names we get the errors. How to fix this issue. - please help?Thanks

  • @TheWeteyezz
    @TheWeteyezz ปีที่แล้ว

    Can you make a video explaining table/list .buffer... how and when to use them?

  • @kaushikbarot9653
    @kaushikbarot9653 ปีที่แล้ว

    Good use case of projections?

  • @kaushikbarot9653
    @kaushikbarot9653 ปีที่แล้ว

    Can we use meta multiple times or use meta in list or record which can provide output of different steps?

  • @rhaps2008
    @rhaps2008 ปีที่แล้ว

    all new tricks thank you so much

  • @emilmubarakshin49
    @emilmubarakshin49 ปีที่แล้ว

    knew a few first tricks - from your previous videos but not the last ones...

  • @Anthony_Lecoq
    @Anthony_Lecoq ปีที่แล้ว

    Meta was new for me. Impatient to test it and to see if it keeps query folding ;)

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว

      Please drop in a comment. Let me know what you find :)

  • @HEMENDRAPARIDA
    @HEMENDRAPARIDA ปีที่แล้ว

    All the videos are awesome.. will subscribe for a paid one soon. 🎉

  • @hemant_pal
    @hemant_pal ปีที่แล้ว

    All are awesome tricks

  • @girishsawant8263
    @girishsawant8263 ปีที่แล้ว

    I'm unable to see Display Option in the Advance editor in power query...without which I'm unable to see steps numbering. How to activate this option. I have Excel 2019 version. Please help.
    Thank You !!

  • @mohamedfouad7958
    @mohamedfouad7958 ปีที่แล้ว

    Thank you for these new tricks of coding in queries

  • @lovlyhearts288
    @lovlyhearts288 ปีที่แล้ว

    How to import an excel file from a sharepoint folder / web without Transform sample file queries? I am expecting a view and single query like when I import file from the local desktop.

  • @HachiAdachi
    @HachiAdachi ปีที่แล้ว

    Didn't know #2 (metadata method), #6 (is) and #7 (&)... Proud that I actually knew 4 out of 7, but it's still 57% = F grade... 😔 Nonetheless, thank you, Chandeep!

  • @StephanBenne
    @StephanBenne ปีที่แล้ว

    The Meta trick is great, I was looking for that.

  • @baskarmani3573
    @baskarmani3573 ปีที่แล้ว

    Hi Chandeep,
    Could you upload the excel sheet with which demo is made. It would help us lot for practicing....

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว

      goodly.co.in/bet-you-dont-know-all-these-power-query-tricks/

  • @Bhavik_Khatri
    @Bhavik_Khatri ปีที่แล้ว

    Very nice tricks

  • @christopherskyap8985
    @christopherskyap8985 ปีที่แล้ว

    Hi Sir, how about csv files ?
    = Csv.Document(File.Contents("d:\Downloads\Monthly-taxi-population.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]) ?

  • @JJ_TheGreat
    @JJ_TheGreat ปีที่แล้ว

    11:01 Do all have to be the same data type, in order to be combined?

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว

      Yes, the data types must match in order to merge them.

  • @jamesmoran3573
    @jamesmoran3573 ปีที่แล้ว

    How zoom in and out works while explaining? Wt tool to use bro please some one help on this.

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว +1

      Ctrl Shift + to zoom in
      Ctrl Shift - to zoom out
      Highlighting tool - Zoomit

    • @jamesmoran3573
      @jamesmoran3573 ปีที่แล้ว

      @@GoodlyChandeep thanks a lot.

  • @KhemitPrince
    @KhemitPrince ปีที่แล้ว

    Second guessed if I was subscribed, love it 😂

  • @zahir585
    @zahir585 ปีที่แล้ว

    Bad ass 💪🏻🙌🏻🙌🏻🙌🏻🙌🏻

  • @muzniahamed5887
    @muzniahamed5887 ปีที่แล้ว

    Hi sir
    I am from Sri Lanka
    I have a question about power query
    "HOW TO REDUCE THE LODING TIME IN POWER QUERY" please reply this sir
    I am facing big issue in PQ

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว

      This video might be useful to you.
      th-cam.com/video/4S_aore7ezg/w-d-xo.html

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

    I knew only the first one!!!

  • @workstuff5253
    @workstuff5253 ปีที่แล้ว

    Loved the error message!! 🤣

  • @JJ_TheGreat
    @JJ_TheGreat ปีที่แล้ว

    6:04 That isn’t true, though! I am subscribed to your channel! What is the actual error?

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว

      😜 Table.Combine function needs a list full of tables.

  • @tommynguyen4253
    @tommynguyen4253 ปีที่แล้ว

    Not received the email to download file 😢

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว

      Can you please check your spam folder

    • @tommynguyen4253
      @tommynguyen4253 ปีที่แล้ว

      @@GoodlyChandeep first email has no link. I have to click subscribe second to get the email with link…

    • @GoodlyChandeep
      @GoodlyChandeep  ปีที่แล้ว

      There could be a technical issue. To download the file, click this link.
      goodly.co.in/wp-content/uploads/dlm_uploads/2023/05/PQ-Tricks.zip

  • @txreal2
    @txreal2 ปีที่แล้ว

    Aha. I did not get the “..viewer not subscribed” error. 😊

  • @riahon
    @riahon ปีที่แล้ว

    LOL @5:58

  • @sudbn
    @sudbn ปีที่แล้ว

    I learned meta tricks from twitter.

  • @fouadhoblos3611
    @fouadhoblos3611 ปีที่แล้ว

    I hate power query.. tons of problems

  • @natsarev
    @natsarev ปีที่แล้ว

    Can't imagine my reaction If I got an error about subscriber in real life 😂