Power Query - Dynamic Column Selection

[@HVACALC]

This channel is an easy subscribe. No fluff ever + high quality concise expert content.

[@bcti-bcti]

@@HVACALC Thank YOU for such nice compliments! We really appreciate your subscribership (if that’s a real word.)

[@jazzista1967]

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-bcti]

@@jazzista1967 My pleasure. Thanks for taking the time to watch and comment.

[@jazzista1967]

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-bcti]

@@jazzista1967 What are you drawing your data from? What is the source?

[@jazzista1967]

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-bcti]

@@jazzista1967 Which step are the hardcoded column names appearing? Ex: a data typing step; a column rename step; a pivot/unpivot step?

[@vramarathnam]

Thank You very much. Your teaching is an eyeopener. I Learnt a Lot.

[@bcti-bcti]

Thank you so much for the compliment. I'm glad you find it helpful. Thanks for watching.

[@Bienvo0921]

As always, you are an excellent PROFESSOR!

[@bcti-bcti]

@@Bienvo0921 Thank you!!!!!!

[@Paladin101]

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-bcti]

@@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.

[@clay4gli]

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-bcti]

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.

[@ericliu79]

Great one. my alternative : =Table.AddColumn(Source, "Custom", each List.Average(List.Skip(Record.ToList(_),1)))

[@bcti-bcti]

That is another good way, but be careful that the position of the field doesn't change. Of course, you could say the same thing about my solution in that, "you better hope the field name doesn't change." Every method has risk. Thanks for watching.

[@ericliu79]

thanks.

[@FsoOmar]

That's what I've been waiting for. Thank you a zillion! 👍👏👏👏

[@bcti-bcti]

@@FsoOmar You are welcome… a zillion!

[@Bhavik_Khatri]

Excellent tutorial.

[@bcti-bcti]

@@Bhavik_Khatri Thank you so much for saying so!

[@Bhavik_Khatri]

@bcti-bcti No worries. We are all on the same path of continuous learning and development.

[@SamehRSameh]

Marvelous 🎉🎉 we need more and more

[@bcti-bcti]

Thank you! I'll do my best to keep producing.

[@zzota]

Brilliant! I'll definitely be using this at work.

[@bcti-bcti]

Glad to hear it helped. Thanks for watching.

[@robbe58]

TOP 💯 Very well explained (in detail), showing all the steps needed to execute and get this result.

[@bcti-bcti]

@@robbe58 thank you so much for your continued support.

[@kkravch]

Excellent PROFESSOR, fully agree, nothing to add... :)

[@bcti-bcti]

@@kkravch Thank you!

[@chrism9037]

So well-explained, thank you

[@bcti-bcti]

@@chrism9037 you are quite welcome. Thanks for your support.

[@excel_along_the_way]

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-bcti]

@@excel_along_the_way I don’t see why it wouldn’t. Thanks for watching.

[@ExcelWithChris]

Thanks my friend. Greetings from South Africa.

[@bcti-bcti]

@@ExcelWithChris Greeting. Hope all is going well. Thanks for taking the time to watch.

[@rajanpradeepankarath8846]

this is brilliant!

[@bcti-bcti]

Thanks! Glad you enjoyed it.

[@tomstrack42]

Excellent, thank you!!!

[@bcti-bcti]

Thank YOU for watching and commenting.

[@RichardJones73]

Nice explanation

[@bcti-bcti]

@@RichardJones73 thank you!

[@Adam_K_W]

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-bcti]

Such nice words. Thank you for watching and your support.

[@Adam_K_W]

@@bcti-bcti I use your solutions very often in my work. They are game changers every time.

[@yousrymaarouf2931]

Fantastic

[@bcti-bcti]

@@yousrymaarouf2931 thank you!!!

[@danilomassarelli1644]

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-bcti]

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.

[@danilomassarelli1644]

​@@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

[@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.

[@nickmorris8993]

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) ) )

[@nickmorris8993]

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-bcti]

FANTASTIC! I really like this method as it is not name-dependent (like you mentioned in your other comment.) Thanks for making this better.

[@JenMayB]

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-bcti]

@@JenMayB it’s always great to see how others solve the same problem but from a different approach. Thanks for watching.