Great example on how to work with lists in Power Query. Lists are extremely powerful and very under used, Thanks for posting these great examples!
@bcti-bcti7 күн бұрын
@@jazzista1967 My pleasure. Thanks for taking the time to watch and comment.
@jazzista19677 күн бұрын
Good morning Byron! Thanks . Watching your examples before heading to the office! Man, you really have great tutorilas! . Do you happen to have a example on how to avoid using hardcoded column names using Table.ColumnNames . I have a very fragile query that if i add a column ,my query will break. Thanks and have a great day
@bcti-bcti7 күн бұрын
@@jazzista1967 What are you drawing your data from? What is the source?
@jazzista19676 күн бұрын
Hello! Sorry for the late reply. I dump the data in excel files in a dedicated folder. Right now, the consolidated files works however its fragile because it has all the column names hardcoded in red font.
@bcti-bcti6 күн бұрын
@@jazzista1967 Which step are the hardcoded column names appearing? Ex: a data typing step; a column rename step; a pivot/unpivot step?
@HVACALC7 күн бұрын
This channel is an easy subscribe. No fluff ever + high quality concise expert content.
@bcti-bcti7 күн бұрын
@@HVACALC Thank YOU for such nice compliments! We really appreciate your subscribership (if that’s a real word.)
@Paladin1018 күн бұрын
A couple of weeks late on this one for timing 😉 but I’m going to use this instead of the solution I’ve implemented. Thanks again 👍
@bcti-bcti8 күн бұрын
@@Paladin101 Yeah, sorry about that. I was thinking all along, “I bet Paladin101 is wishing I’d finish this one up already.” 🤣 Thanks for watching.
@Bienvo09219 күн бұрын
As always, you are an excellent PROFESSOR!
@bcti-bcti9 күн бұрын
@@Bienvo0921 Thank you!!!!!!
@clay4gli7 күн бұрын
Great video. Many other creators make this much harder with their approach. Your solution seems much easier to follow. I am eager to try it. Thank you.
@bcti-bcti6 күн бұрын
There is even an easier way to do this (see the video description), but I wanted to use this explore other PQ features. Thanks for watching.
@SJGorillaКүн бұрын
It’s much easier to create a new query with a list using headers and remove the first column header to get dynamic list of months and replace the hard coded month headers with this new list.
@chrism90378 күн бұрын
So well-explained, thank you
@bcti-bcti8 күн бұрын
@@chrism9037 you are quite welcome. Thanks for your support.
@excel_along_the_way8 күн бұрын
Thank you, this is very helpful and a method I would definitely use. This should even work if you want to keep your monthly data when you import data from external source.
@bcti-bcti8 күн бұрын
@@excel_along_the_way I don’t see why it wouldn’t. Thanks for watching.
@zzota6 күн бұрын
Brilliant! I'll definitely be using this at work.
@bcti-bcti6 күн бұрын
Glad to hear it helped. Thanks for watching.
@RichardJones739 күн бұрын
Nice explanation
@bcti-bcti9 күн бұрын
@@RichardJones73 thank you!
@Bhavik_Khatri8 күн бұрын
Excellent tutorial.
@bcti-bcti8 күн бұрын
@@Bhavik_Khatri Thank you so much for saying so!
@Bhavik_Khatri8 күн бұрын
@bcti-bcti No worries. We are all on the same path of continuous learning and development.
@Adam_K_W8 күн бұрын
Amazing as always! You're out there solving problems that people really need solved and your manner and presentation skills are Excellent! Thanks for all you do! That said, I have a challenge for you related to this scenario. I work with Forecast files with variable numbers of monthly columns setup much like this. Part 1. There are roughly 43 columns of different values before we get to the actual monthly columns (Task Codes, P.O. Numbers, etc.). ? Do I need to list each of the individual field names in the "Record.Remove Fields" section or can I do that differently? Part 2. Here's where it gets tricky... My forecast files are for projects in process. Some of the columns are for months in the past (where actual costs are summed up) and the remaining columns are for the future months/balance to finish. Each month, when we update the forecast period, the current month is visually moved to the prior month via formatting those cells gray, with the remaining forecast months cells are yellow. ?: Is there a way to dynamically create the list of ONLY the future months and ignore the monthly columns in the past?
@bcti-bcti8 күн бұрын
Such nice words. Thank you for watching and your support.
@Adam_K_W8 күн бұрын
@@bcti-bcti I use your solutions very often in my work. They are game changers every time.
@ExcelWithChris8 күн бұрын
Thanks my friend. Greetings from South Africa.
@bcti-bcti8 күн бұрын
@@ExcelWithChris Greeting. Hope all is going well. Thanks for taking the time to watch.
@robbe588 күн бұрын
TOP 💯 Very well explained (in detail), showing all the steps needed to execute and get this result.
@bcti-bcti8 күн бұрын
@@robbe58 thank you so much for your continued support.
@SamehRSameh8 күн бұрын
Marvelous 🎉🎉 we need more and more
@bcti-bcti8 күн бұрын
Thank you! I'll do my best to keep producing.
@SndfOmar6 күн бұрын
That's what I've been waiting for. Thank you a zillion! 👍👏👏👏
@bcti-bcti6 күн бұрын
@@SndfOmar You are welcome… a zillion!
@kkravch7 күн бұрын
Excellent PROFESSOR, fully agree, nothing to add... :)
@bcti-bcti7 күн бұрын
@@kkravch Thank you!
@tomstrack428 күн бұрын
Excellent, thank you!!!
@bcti-bcti8 күн бұрын
Thank YOU for watching and commenting.
@nickmorris89937 күн бұрын
Excellent solution and explanation. As an alternative, how about this single step? Table.AddColumn ( Source , "Avg Sales" , each List.Average ( List.RemoveFirstN (Record.ToList(_),1) ) )
@nickmorris89937 күн бұрын
Perhaps the slight advantage of this version is that we don't need to name the "SalesPerson" column, so it will still work even if the column header gets changed. Just a thought ...
@bcti-bcti7 күн бұрын
FANTASTIC! I really like this method as it is not name-dependent (like you mentioned in your other comment.) Thanks for making this better.
@JenMayB9 күн бұрын
I would highlight the first column and select unpivot other columns and then pivot them. Or i would create a list step from table.getheaders (or something to that effect) and then load that list step into the average step.
@bcti-bcti8 күн бұрын
@@JenMayB it’s always great to see how others solve the same problem but from a different approach. Thanks for watching.
@danilomassarelli16445 күн бұрын
Hi, can I ask for you a technical question? are there any alternative solutions to create a column of records with use the Table.AddColumn function instead to use underscore (_) ? Thanks so much in advance for your help
@bcti-bcti5 күн бұрын
If there is, I'm not aware of it. Let me know if you find another way so we can both learn something. Thanks for watching.
@danilomassarelli16445 күн бұрын
@@bcti-bcti I used this solution but I was finding a PQ function to use. I have add a new index column and then i wrote this code: Table.AddColumn(Idx, "Rcd", each Idx{[Indice]}, type record). But I don't like it I would like an other solution. Who knows other solutions? thx
@rajanpradeepankarath8846Күн бұрын
this is brilliant!
@bcti-bctiКүн бұрын
Thanks! Glad you enjoyed it.
@yousrymaarouf29318 күн бұрын
Fantastic
@bcti-bcti8 күн бұрын
@@yousrymaarouf2931 thank you!!!
@SJGorillaКүн бұрын
It’s much easier to create a new query with a list using headers and remove the first column header to get dynamic list of months and replace the hard coded month headers with this new list.
@bcti-bctiКүн бұрын
@@SJGorilla That’s the great thing about Power Query: there’s always more than one way to solve the problem. Thanks for watching.