The Magic of working with Records in Power Query
HTML-код
- Опубликовано: 23 июл 2024
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, you will learn about
- What is a Record in Power Query
- Extracting Records from a Table
- Creating Records from Scratch
also, there are two practical examples of using Records that might be helpful in creating dynamic solutions.
===== 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/the-magic-workin...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== CHAPTERS =====
0:00 Intro
0:43 Understanding Records in Power Query
1:51 Extracting a Record from a Table
3:01 Creating a Record from Scratch
4:19 Example 1 - Summing existing columns to Create a New Column
6:54 Example 2 - Extract Steps from Power Query
9:37 My Courses
===== 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!
- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
- Source: goo.gl/fh3rEJ
Official After The Fall RUclips Channel Below
ruclips.net/channel/UCGQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses 
Наука
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.
Perfect!!! That way of extracting a particular step from a query is more than awesome! I can't thank you enough!
Glad you liked it!
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.
Technique to extract steps as field of record is super cool!!
Thank You!
It is blow my mind and thank you chandeep for your great work
Simple concepts, very valuable information. Thanks for this video.
Most welcome!
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!
Happy to hear that!
A mind blowing start to 2023! Thanks so much for this video.
Happy new year!
You were not wrong: the last method did blow my mind. Great stuff! Thank you!
Great to hear!
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.
Glad you liked it!
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!
Thanks! Glad you like this!
Wow. That's impressive. Thank you for all your great videos.
Glad you like them!
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!
Thanks! Glad you like these contents !
Happy new year Goodly!!!
As always rocked !!!!
Happy new year!!
Happy News year and the ew year starts with your great content and amazing Knowledge.Thank you very much
Happy new year!
Thanks for sharing the basic idea of what actually a RECORD is...
Glad it was helpful! Ajay!
That's awesome! I didn't know you could do this kind of thing.
Thanks!
Again helpful video.
Tysm
Happy new year
Happy new year!
Awesome, Chandeep. Thanks!
Glad you liked it!
Amazing!
thinks for your effort , it so helpful
Glad it was helpful!
Master piece !! 👌👏🏾
Glad you like this, Alain!
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
HNY!! And this is indeed mind blowing! Great one!
Happy new year!
Excellent!!!
Glad you like it!
Awesome content!
Glad you enjoyed it,
kreshunram!
Hi Chandeep!, Happy New Year to you and all of your subscribers😀. Thank you very much for sharing Great stuff!
Happy new year!
Great video!!
Thanks!
Great video
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
Sure, Happy New Year!
Awesome!
Thanks!
Another master piece
Thanks!
Superb
You are a genius ✨
Glad you think so Ndrtim!
Thanks for sharing ❤
Glad you like it !
Great Idea 🤩🥇🚀
Thanks!
Genius!
Thank You!
chandeep bhaaii guruji chaa gaye
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.
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
Marvelous
Thank You!
minute 8:40 just blowed mi mind
Thanks
Thanks a lot :)
You are marvelous 😘
Thank you so much 😀
Awesome!!! : O ( 09:23 )
Thank You!
Thank you bro :D
Happy to help!
Bhai moj ker de apne ❤
first time pta chla ki power query me be sum ho jata hai
Wow bhai ji ❤
Glad you liked it!
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!
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.
@@paulgallagher2987 Yes, exactly!
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.
I don't thing you can. However you can use the ToCsv dax function to generate a csv file.
@@GoodlyChandeep Thank you very much for your reply. I really appreciate it.
Hi Bro, Please help me to resolve my query. how can i apply hash function based on current logged in user in m query.
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.
Got it, man this is magic, thank you so much.
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!
M[ aravilloso]...
Thanks! 😄
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!..
List.Skip starts the counting from 1
Otherwise generally the indexing starts from 0
sir urdu maay bhi stuff banaaay it is a great job for urduu liistner
👏
Thanks!
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.
Couldn't smash the like button enough, unfortunately 🙂
this guy is M God
Thanks!
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
how to expand record? please help
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.
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 ...
I'm sorry but I haven't understood your question here.
So your a PQ/BI developer huh? That means you follow Chandeep “Goodly” tutorials and use his tricks…
Guilty as charged!
haha.. did someone really say that? I'd be surprised!
thanks a lot!
Khas Hindi me sikhate 😢
Marvelous
Thanks!