Download the File ⬇ - goodly.co.in/explore-magic-of-records/ Join the waitlist for Power Query's M Language course↗ - subscribepage.io/Learn_Power_Querys_M_Language
Yes anil it is definitely fun n addictive. I spend more time on it than my actual test automation work 😁. I hope i could switch to this lovely thing PQ
Using records is also great for adding many columns in one step, then just expand the record to new columns. Reworked many a query where there have been multiple (sometimes > 10) #"Add Column" steps down to two steps - one for creating the record with each field (column) and one to expand the record to columns
How did you populate the values in the columns? I assume it wasn't a static value, so were you able to use a formula? I'm really interested how you did this.
@@larmondoflairallen4705 A table with two date columns named "Order Date" and "Due Date" - add two new columns calculating the age and an age bucket value. These steps need to be added via the Advanced Editor AddRecord = Table.AddColumn(Previous_Step_Name, "Record", each [ Days = Duration.Days([Due Date] - [Order Date]), Age Bucket = if Days > 14 then "Overdue" else "Current" ], type [ Days = Int64.Type, Age Bucket = text ] ), ExpandRecord = Table.ExpandRecordColumn(AddRecord, "Record", {"Days", "Age Bucket"} )
Again some very interesting and useful knowledge. I work with teams who ask me for solutions, the teams are not always that good at saying what they want. I think I could use this to allow them to see which data in the calcuations are giving the true values and if they are working as they expect.
That was great, just the getting the record column as a true false could be so useful, have you thought about doing a video on what the MVP qualification covers, I think a lot of people would be interested unless MS have some rules regarding the matter.
With the current workbook you can only input named ranges, so if you make the date a named range it can work. If you create a new workbook and link the previous workbook, you can then pull in the sheet you want even if there is no named range.
Hi, need some help here. I have a source from share point. I need to filter out some data for which i use merge query, but I get a privacy issue error. How to deal with it? If i make a new query with the loaded data and merge it, no issue. But in this method, I'm unable to replace the existing data, so I'll be getting two tables, one full data and one filtered data. Can you guide me on how to approach this issue?
@GoodlyChandeep Hi I need help. i am developing a dashboard for Collections efficiency. i facing challenge , i want a table to display the aging of currant month and last few months and this should be dynamic based on today's date. if today is 16 oct then column name should be October, if today is 15th oct then column name should be Sep 2023. i created a measure and new parameter for changing column names dynamically. but when i am using the filter options like show data for last 4 months, the table names are not changing. and new parameter created is not interactive with the slicer.. please help me to solve this..
I have a question regarding merge queries. When I merge varies and expand the columns I am getting some columns as null. There is question mark at the data type icon. Can you please help me with same I am stuck from long time
Hi sir... could you please help me how to prepare a automated GST invoice series in power query for GST R-1 ... recently I am trying to do this but still I am not able. If you can any help, I will be grateful to you...
@GoodlyChandeep Hi I need help. i am developing a dashboard for Collections efficiency. i facing challenge , i want a table to display the aging of currant month and last few months and this should be dynamic based on today's date. if today is 16 oct then column name should be October, if today is 15th oct then column name should be Sep 2023. i created a measure and new parameter for changing column names dynamically. but when i am using the filter options like show data for last 4 months, the table names are not changing. and new parameter created is not interactive with the slicer.. please help me to solve this..
Download the File ⬇ - goodly.co.in/explore-magic-of-records/
Join the waitlist for Power Query's M Language course↗ - subscribepage.io/Learn_Power_Querys_M_Language
Jab promo trailer itna acha hai toh movie kitni achi hogi.... All the best Chandeep for upcoming M code. Lots of love to goodly ❤
Okay now power query is becoming addictive thanks to you. Loving to use it as much as possible in regular work...
Thanks Ani :)
Yes anil it is definitely fun n addictive. I spend more time on it than my actual test automation work 😁. I hope i could switch to this lovely thing PQ
@@shirshN instead of simply copy paste, I'm using PQ for every small thing 😂
Amazing! Besides the main topic of the video I´m thrilled to learn that you can have if-clauses in the in-statement. Thank you!
⚠ Having a calculation (any calculation) in the in-statement will "hide" all the steps in the Query Pane
The best possible explanation of records. Very bright, as always. Thank you.
Using records is also great for adding many columns in one step, then just expand the record to new columns.
Reworked many a query where there have been multiple (sometimes > 10) #"Add Column" steps down to two steps - one for creating the record with each field (column) and one to expand the record to columns
How did you populate the values in the columns? I assume it wasn't a static value, so were you able to use a formula? I'm really interested how you did this.
@@larmondoflairallen4705 A table with two date columns named "Order Date" and "Due Date" - add two new columns calculating the age and an age bucket value. These steps need to be added via the Advanced Editor
AddRecord = Table.AddColumn(Previous_Step_Name,
"Record",
each [
Days = Duration.Days([Due Date] - [Order Date]),
Age Bucket = if Days > 14 then "Overdue" else "Current"
],
type [
Days = Int64.Type,
Age Bucket = text
]
),
ExpandRecord = Table.ExpandRecordColumn(AddRecord,
"Record",
{"Days", "Age Bucket"}
)
This technic is an eye opener for me and opens up lot of other possibilities. Thanks for sharing Chandeep!
Again some very interesting and useful knowledge. I work with teams who ask me for solutions, the teams are not always that good at saying what they want. I think I could use this to allow them to see which data in the calcuations are giving the true values and if they are working as they expect.
Great idea Chandeep! 👏
Never thought of using records like this! 💡
Thank you for sharing this!
Bloody brilliant. I also love the comment about using records for Add columns.
Sharp observation but I have a few more tricks on column additions
This is a great trick Chandeep, especially when building something new and validating the granularity of the data. Love it! 🔥
Awesome! Thank you! good to learn also that you can have an if in the in statement!
So creative. Total is more than the sum of its parts.
I love you videos.
Great 💯👍
You are truly a PQ genius
Dark theme with sunny shining bandana is super cool👏😁
very, VERY good Chandeep! bahut acha man! Thank you
Nice way 👍🏻 will be helpful in some calculation in daily reporting
Thank you sir from Zimbabwe
That was great, just the getting the record column as a true false could be so useful, have you thought about doing a video on
what the MVP qualification covers, I think a lot of people would be interested unless MS have some rules regarding the matter.
Very very smart way of working Goodly 😊
Intresting....
Neat, thank you 😊 👌🏽.
Great video. Very interesting use of records.
Super duber bro !!!
Zabardast.....love you
Danke!
Many Thanks!
Most excellent, sir
Chandeep you don’t stop amazing us with you brilliant ideas. Can I sign up for this course please?
Yeah. Why not :)
WOW! AWESOME!!!! 👏
Interesting 🎉.. thanks for sharing 😊
Interesting techniques.
WOW !
Great tutorials, can you do a tutorial how to get date from current workbook, specific worksheet without creating a table?
With the current workbook you can only input named ranges, so if you make the date a named range it can work. If you create a new workbook and link the previous workbook, you can then pull in the sheet you want even if there is no named range.
Brilliant!
Excellent!
Nice one as always.. 🤟
Brilliant ❤
Hi, need some help here. I have a source from share point. I need to filter out some data for which i use merge query, but I get a privacy issue error. How to deal with it?
If i make a new query with the loaded data and merge it, no issue. But in this method, I'm unable to replace the existing data, so I'll be getting two tables, one full data and one filtered data. Can you guide me on how to approach this issue?
@GoodlyChandeep Hi I need help. i am developing a dashboard for Collections efficiency. i facing challenge , i want a table to display the aging of currant month and last few months and this should be dynamic based on today's date. if today is 16 oct then column name should be October, if today is 15th oct then column name should be Sep 2023. i created a measure and new parameter for changing column names dynamically. but when i am using the filter options like show data for last 4 months, the table names are not changing. and new parameter created is not interactive with the slicer.. please help me to solve this..
Brilliant
Awsome 😮
Awesome❤
I have a question regarding merge queries. When I merge varies and expand the columns I am getting some columns as null. There is question mark at the data type icon. Can you please help me with same I am stuck from long time
Hi sir... could you please help me how to prepare a automated GST invoice series in power query for GST R-1 ... recently I am trying to do this but still I am not able. If you can any help, I will be grateful to you...
👏👏👏
Unbelievable
Chandeep sir, Parvinder sir ko credit do, Same video create kiya hai aapne
I asked you my data on email, but you did not respond.
Sir please give some discount on your course BI
2:35
@GoodlyChandeep Hi I need help. i am developing a dashboard for Collections efficiency. i facing challenge , i want a table to display the aging of currant month and last few months and this should be dynamic based on today's date. if today is 16 oct then column name should be October, if today is 15th oct then column name should be Sep 2023. i created a measure and new parameter for changing column names dynamically. but when i am using the filter options like show data for last 4 months, the table names are not changing. and new parameter created is not interactive with the slicer.. please help me to solve this..