The Magic of working with Records in Power Query

[@curtismusson2760]

I appreciate the way you write custom column formulas piece by piece. The first example in this video demonstrates what the underscore really does. It was a lightbulb moment for me.

[@raimundojs9547]

Perfect!!! That way of extracting a particular step from a query is more than awesome! I can't thank you enough!

[@GoodlyChandeep]

Glad you liked it!

[@ThepExcel]

Technique to extract steps as field of record is super cool!!

[@GoodlyChandeep]

Thank You!

[@larmondoflairallen4705]

I had no idea you could sum up columns using records. This video, the one about lists, and the one about the "each" keyword really fill in some knowledge gaps for me. Awesome content!

[@GoodlyChandeep]

Thanks! Glad you like these contents !

[@Sumanth1601]

Hey Chandeep! Just wanted to reach out and say thanks for the great video on records in Power Query. It was super helpful and informative, and I really appreciated how clear and concise the explanations were. The examples were easy to follow and I feel much more confident in using records now. Thanks for sharing such a valuable resource!

[@GoodlyChandeep]

Thanks! Glad you like this!

[@andrejkrecker5639]

Especially the last one is sooo useful. Conbine this with your "advanced group by tricks" Video and your mastering a lot of data analytics, while reducing queries immensly. Thanks for sharing this!

[@GoodlyChandeep]

Happy to hear that!

[@NancyPickering]

A mind blowing start to 2023! Thanks so much for this video.

[@GoodlyChandeep]

Happy new year!

[@paulgallagher2987]

I've just revisited this video and my initial thought for example 1 was why didn't you just hard code the column names so the add column code is a simple and readable List.Sum({[Jan],[Feb],[Mar],[Apr]}) Then your genius struck me! I realised that your method will still work when the source data has columns in for May, June, etc. I should know never to question you Chandeep! :D

[@scottcha03]

You were not wrong: the last method did blow my mind. Great stuff! Thank you!

[@GoodlyChandeep]

Great to hear!

[@paulgallagher2987]

This is awesome! Thanks so much Chandeep. That last example of being able to reference a particular interim step is so useful. So much better than my existing workaround.

[@GoodlyChandeep]

Glad you liked it!

[@naveenmedishetty5993]

It is blow my mind and thank you chandeep for your great work

[@ajayv304]

Thanks for sharing the basic idea of what actually a RECORD is...

[@GoodlyChandeep]

Glad it was helpful! Ajay!

[@IvanCortinas_ES]

Simple concepts, very valuable information. Thanks for this video.

[@GoodlyChandeep]

Most welcome!

[@nelson_k_d]

Thanks a ton for the awesome videos & the great explanation you do, I've learnt a lot from your videos. I tried the steps you shared for converting the steps in a query to records, but unfortunately, an error was returned: Formula.Firewall: Query 'QueryName' references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. No clue on how to proceed..The files are picked from SharePoint

[@debbie6085]

Wow. That's impressive. Thank you for all your great videos.

[@GoodlyChandeep]

Glad you like them!

[@venkat8071]

Hi Chandeep!, Happy New Year to you and all of your subscribers😀. Thank you very much for sharing Great stuff!

[@GoodlyChandeep]

Happy new year!

[@arunkumarkesavan6077]

Happy new year Goodly!!! As always rocked !!!!

[@GoodlyChandeep]

Happy new year!!

[@ujwalreddyd]

Hi Sandeep, first of all, Kudos to your channel and your efforts. There are many thing to appreciate about your content. The content is crisp, web page with the content, your speech clarity, quality of the video itself (background etc.). This is the first video I was few days back and I have watched many of your videos. I have one question regarding Excel/PowerQuery from many days. Can we generate a file (let's say a text file) from Excel/PowerQuery. Thank you very much in advance.

[@GoodlyChandeep]

I don't thing you can. However you can use the ToCsv dax function to generate a csv file.

[@ujwalreddyd]

@@GoodlyChandeep Thank you very much for your reply. I really appreciate it.

[@jerrydellasala7643]

Chandeep, I've looked but haven't found anything like this, and I think it's something right up your alley. Imagine you have 3 tables with many rows of around 12 items, and 12 to 20 columns in each table. What you're looking to do is to Append the data of the 3 tables, then group them by the 12 items, and then get the sum of the values in the other 11-19 columns. Now this can be done through the UI, but that means having to manually aggregate the SUM of each of the 12 columns. I suspect there's a way to use the column headers as well as the items in the first column to get the total for each item by each column. What would that be? Thanks.

[@williamarthur4801]

Happy New Year, These videos on theory are very important when starting as so often you can see a term used but if no ones explained what it means it can be so confusing especially when starting, examples and exercises are great, but as A.F and M.R insist we have to understand the theory.

[@karimallahwala7022]

Happy News year and the ew year starts with your great content and amazing Knowledge.Thank you very much

[@GoodlyChandeep]

Happy new year!

[@GosCee]

Happy New Year, Chandeep! Nicely explained. I am saving this one in my playlist because I am sure I will have use for it later. Thanks

[@GoodlyChandeep]

Sure, Happy New Year!

[@abhijeetshetye3764]

Again helpful video. Tysm Happy new year

[@GoodlyChandeep]

Happy new year!

[@elbadlis]

Awesome, Chandeep. Thanks!

[@GoodlyChandeep]

Glad you liked it!

[@gurjar5708]

Bhai moj ker de apne ❤ first time pta chla ki power query me be sum ho jata hai Wow bhai ji ❤

[@GoodlyChandeep]

Glad you liked it!

[@surjaa.c]

Just a few days ago I was wondering how to extract an intermediate step from a query. I finally gave up on it and duplicated the query instead (which was obviously not as efficient). I wish I had seen this video earlier!

[@paulgallagher2987]

Yep. I'm exactly the same. It was frustrating duplicating it because I knew I was making my life more difficult in the future when it came to maintaining the code.

[@surjaa.c]

@@paulgallagher2987 Yes, exactly!

[@JJ_TheGreat]

That's awesome! I didn't know you could do this kind of thing.

[@GoodlyChandeep]

Thanks!

[@mirrrvelll5164]

HNY!! And this is indeed mind blowing! Great one!

[@GoodlyChandeep]

Happy new year!

[@robertlevak]

That's impressive! In example2 can you remove some of the steps of records in the end, let's say I only want some of the data. For example, I want to remove steps from records for better visibility, i just want Source, Renamed colums and grouped rows. I don't want to lose those steps, I just don't want to see them.

[@robertlevak]

Got it, man this is magic, thank you so much.

[@robertlevak]

I opened a new blank query, then with Record.ToTable I got data that I can filter. I opened another help query, with one row, called Number_Path, it's help to get the row number from the sheet. List with row number. let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], Number_Path1 = Source{0}[Number_Path] in Number_Path1 Now start the Goodly Magic! = #"Filtered rows"{Number_Path}[Value] From sheet with drop down list I choose which table I want, I have 9 tables and only one query to refresh. In one sheet :) And when a value changes in a cell, the VBA code performs a refresh :) Very cool and dynamic for presenting data. Thanks Mr. Goodly!

[@modernboutique1389]

Another master piece

[@GoodlyChandeep]

Thanks!

[@fe_nicks]

Hello, Chandeep! Why do you put "1" in List.Skip function (6:29) to skip first element if indexing in PQ starts from zero?! However, the function works like it supposed to.. Little confused. Thank you!..

[@GoodlyChandeep]

List.Skip starts the counting from 1 Otherwise generally the indexing starts from 0

[@vishnupp5944]

Great video

[@kreshunram]

Awesome content!

[@GoodlyChandeep]

Glad you enjoyed it, kreshunram!

[@abhishek12865a]

chandeep bhaaii guruji chaa gaye

[@rajkumarrajan8059]

Excellent!!!

[@GoodlyChandeep]

Glad you like it!

[@aynino]

Master piece !! 👌👏🏾

[@GoodlyChandeep]

Glad you like this, Alain!

[@ndrtimdauti4173]

You are a genius ✨

[@GoodlyChandeep]

Glad you think so Ndrtim!

[@prasadk5082]

Superb

[@EricaDyson]

Amazing!

[@yahyalouizi2695]

thinks for your effort , it so helpful

[@GoodlyChandeep]

Glad it was helpful!

[@jeromeastier462]

Awesome!

[@GoodlyChandeep]

Thanks!

[@ChristianMartinez-rv5or]

minute 8:40 just blowed mi mind

[@surender665]

Hi Bro, Please help me to resolve my query. how can i apply hash function based on current logged in user in m query.

[@kennethstephani692]

Great video!!

[@GoodlyChandeep]

Thanks!

[@azizahmedkhan669]

please make 1 video on how to use SWITCH case for STRING DATA, i dont want to create a column, rather i want to create a measure with switch case on string data, for Example if Country column value is INDIA i want to show IND value

[@FRANKWHITE1996]

Thanks for sharing ❤

[@GoodlyChandeep]

Glad you like it !

[@KlausTrapp]

Great Idea 🤩🥇🚀

[@GoodlyChandeep]

Thanks!

[@mis-g7n]

Thanks

[@GoodlyChandeep]

Thanks a lot :)

[@cg24356]

Genius!

[@GoodlyChandeep]

Thank You!

[@wendelsilva7055]

Awesome!!! : O ( 09:23 )

[@GoodlyChandeep]

Thank You!

[@SamehRSameh]

Marvelous

[@GoodlyChandeep]

Thanks!

[@1yyymmmddd]

To reference any previous step in Power Query you just use that step name. No need to go through building the records table. Just don't forget to use #" " if you have blanks or special characters in the step name.

[@hlntuan]

Thank you bro :D

[@GoodlyChandeep]

Happy to help!

[@KuldeepSingh-nq1vi]

You are marvelous 😘

[@GoodlyChandeep]

Thank you so much 😀

[@khalidmajeed2886]

sir urdu maay bhi stuff banaaay it is a great job for urduu liistner

[@QuantumIdeas]

Couldn't smash the like button enough, unfortunately 🙂

[@InformationWoWiase]

👏

[@GoodlyChandeep]

Thanks!

[@KawanExcel]

this guy is M God

[@GoodlyChandeep]

Thanks!

[@Drew-y3f]

Let’s say you want to replace one of the records how would you do that? Let’s say the fourth record you want to replace the values.

[@pirugarden1129]

how to expand record? please help

[@randolfojolongutierrez5311]

M[ aravilloso]...

[@GoodlyChandeep]

Thanks! 😄

[@weav8060]

Ok, deleting the let and in statement is not power query. It's a manual thing. So not sure what youre suggesting about using power query to extract steps; just copy the text of any as needed.. Ie why code the extraction process? It's interesting and informative, but what I thought you were going to say was that there was a two way process, to start a query as a set of records and then translated into query somehow by adding let and in with PQ commands ...

[@GoodlyChandeep]

I'm sorry but I haven't understood your question here.

[@ulyssesdanosos642]

So your a PQ/BI developer huh? That means you follow Chandeep “Goodly” tutorials and use his tricks… Guilty as charged!

[@GoodlyChandeep]

haha.. did someone really say that? I'd be surprised! thanks a lot!

[@Rahul.parmar8439]

Khas Hindi me sikhate 😢

[@ajayv304]

Marvelous

[@GoodlyChandeep]

Thank You!