Відео

"I see only one mistake in this epic explanation of List.Accumulate. When you add a new column 'Stock James,' the value of each record has been added to 'Portfolio Nr of Stocks.'" So the running totals aren't right. Am I right? 🙃🙃

This only works if you do not show other columns.. as soon as I try to add other columns, the blank rows come back. As an example, let's say i wanted to show a currency type for each division in your example. The results will be that the blank rows come back... do you have a solution that can hide the rows despite other columns being available? I tried a visual filter, but seems once i set that, the results are even more unexpected where rows are being hidden when they should not be.

Absolutely spectacular. Certainly one of the best Power Query channels!! Thanks for all the passion! It's well worth it.

Happy new year Senseï 🎉

❤That was a great vid, I have a similar situation and you got me past a major hurdle. Being able to make changes to the data while they are still in the list was brilliant. In my situation, after the tables are cleaned up, I need them to be two separate tables to push into excel. I cannot seem to master expanding the data and saving the tables. My use case is the tables contain some information that is used to calculate later in Excel and I need them as separate tables.

Great! In Added Custom step, what if you don't have a year? Instead you have "Award". What do we use instead of "...each if Value.Is([Column1], type number) ?? Thanks.

thanks for showing an example with the date format, I tried a lot to do as it is written in the official help, nothing worked through square brackets

my side note on this. start with a grouping-Step by year and product to ensure the data is in the expected granularity. that way your division by 12 keeps correct. i ve seen lots of datasets like this with manual added values because of different reasons.

Terrific Video!!

I thought of using List.Dates ; List.Dates( #date([Year] ,1,1),12, #duration(31,0,0,0) ) but you still end up needing transform to get the format, liked the way you added date type using {} had never considered that. Happy New year.

Brillant and excellent Mr BA, YOU ROCK 🙌 what about starting from the monthly split and do it now at a working day level ? Could be an interesting challenge isn’t it ? First count days for every month, divide by working days, and no budget on week end day and bank holidays, all rounding to match the year budgets Are you ready to fire UA-cam power query videos and win the Olympics ? You are the best so wait and see your next step ! Best to you

Excellent, as always. Thank you!!!

Very very usefull lesson. Thanks a lot, Mr BA Sensei!

Another belter! I've struggled a little with the outer/inner structure but your explanation has definitely helped.

the tutorial is GOLD! thanks James for the amazing tips and tricks!

James. Excellent tutorial. You just solved a mystery on how to sort dates monthly ( From Jan thru Dec) .I stopped using the Datetotext function because it was always returning the dates in alphabetical order ( April, August etc) and i did not know how to sort it from Jan to Dec. I wish you could extend this monthly budget exercise Furter to include periods: Lets say your monthly allocation for a product is from Jan thru Oct , then another product from May to Oct , or November to March of the following year and so on. Thanks and Happy new year

Aother cool trick added to my arsenal. Thank you.

Great content. How difficult it is to do the same but for a fiscal year Oct - Sep? Also, I noticed that you keep fighting with automatic change type. While in PQ go to File, Data Load, Type Detection, and select Never detect... Best.

Brilliant as always 👍❤

loved it! 3 new useful things in one video!

Awesome

A have been using the add column with the list transform technique for several years.... but your solution with the dynamic year grab is much stronger an elegant, the explanation of the 'inner' and 'outer' context/table clarified it in a very simple way after many years!!! AND, the declaration of the list type by wrapping it in curly brackets is the cherry on the top (I love finding these gold nuggets in your videos). Thank you very much for sharing your knowledge with us!!

Top !!!!

Another banging vid, Sensei!

What is meaning expression "occurance all"

Great video...thank you!

Pro tutorials. Excellent explanation. Thanks for being so good. Merry Christmas.

Wow that's some sexy sh*t!!!! Excellent video

Do you have a course where you teach power query ?? Because thats mi christmas wish.

I don't understand half of it but still amazing.

I'm humbly amazed by your approach. Amazing!

Genius, I'm just gonna watch all your videos for inspiration and in generall to have broader view when I'm approaching any Power Query problem. Please keep it coming !!!

Hi James, there's 4:11 I saw null value. Also you applied List.RemoveNull function. This should remove this null value on date column but it is still there. Am I right?

Outstanding!!!. I wonder why did you select 30 minutes increments for time calculation. If minutes is used, hao daoes this affects the function?

tks u

Great video! But after getting column with lists and cleaning nulls you may convert all to table with Table.FromRows function :)

Thanks. What if the column headers are different?

thank you

Dang, thats smooth

Very nice to meet you! You earn a new subscriber! Thank you my friend! I love Power Query!

wow

👍👍👍

Great as always. Thanks to data mater and magician James and look forward to more videos 🌹❤👍

There are no friendly datasets! Another great vid. Highly instructional.

Awsome ! Sensei, I love the record-to-list way to master those jagged data. And, the projection movement too.

WOW! That is excellent! Thank you Magician :)

Brilliant!

Excellent video! I ended up in a situation where the power query import of an excel-file jigged the headings and the data by itself. It even removed one column heading 😂

Star 🌟

Great video as usual