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.
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!
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!
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
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!
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.
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
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.
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.
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.
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!..
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.
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!
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.
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.
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
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 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!
Technique to extract steps as field of record is super cool!!
Thank You!
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!
You were not wrong: the last method did blow my mind. Great stuff! Thank you!
Great to hear!
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 !
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
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!
A mind blowing start to 2023! Thanks so much for this video.
Happy new year!
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!
Thanks for sharing the basic idea of what actually a RECORD is...
Glad it was helpful! Ajay!
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
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.
It is blow my mind and thank you chandeep for your great work
after converting a table with many steps in records and will i refer it for making visuals
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.
Simple concepts, very valuable information. Thanks for this video.
Most welcome!
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!
Wow. That's impressive. Thank you for all your great videos.
Glad you like them!
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!
minute 8:40 just blowed mi mind
chandeep bhaaii guruji chaa gaye
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
Hi Chandeep!, Happy New Year to you and all of your subscribers😀. Thank you very much for sharing Great stuff!
Happy new year!
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!
Happy News year and the ew year starts with your great content and amazing Knowledge.Thank you very much
Happy new year!
You are a genius ✨
Glad you think so Ndrtim!
Awesome, Chandeep. Thanks!
Glad you liked it!
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.
HNY!! And this is indeed mind blowing! Great one!
Happy new year!
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.
Happy new year Goodly!!!
As always rocked !!!!
Happy new year!!
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!
Hi Bro, Please help me to resolve my query. how can i apply hash function based on current logged in user in m query.
Another master piece
Thanks!
That's awesome! I didn't know you could do this kind of thing.
Thanks!
Master piece !! 👌👏🏾
Glad you like this, Alain!
Again helpful video.
Tysm
Happy new year
Happy new year!
thinks for your effort , it so helpful
Glad it was helpful!
Awesome content!
Glad you enjoyed it,
kreshunram!
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
Great video
Excellent!!!
Glad you like it!
Great Idea 🤩🥇🚀
Thanks!
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.
Superb
sir urdu maay bhi stuff banaaay it is a great job for urduu liistner
Thanks for sharing ❤
Glad you like it !
You are marvelous 😘
Thank you so much 😀
Amazing
how to expand record? please help
Amazing!
Thanks
Thanks a lot :)
Great video!!
Thanks!
Couldn't smash the like button enough, unfortunately 🙂
Awesome!
Thanks!
Thank you bro :D
Happy to help!
this guy is M God
Thanks!
Awesome!!! : O ( 09:23 )
Thank You!
Genius!
Thank You!
Marvelous
Thank You!
M[ aravilloso]...
Thanks! 😄
👏
Thanks!
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!