I’ve viewed several tutorials regarding dynamically identifying header rows. I can tell you this, to me, is the best solution of its category. To make it complete, kindly create a function that can be applied to multiple files in a single folder at one go. Well done.
I'm working on a version that will process a series of files from a folder. As soon as I have it finished, I'll be posting an update. Thanks for watching.
@@bcti-bcti transformation of a nested table? That's how I do it, I do the transformation in 1 table, then connect to the folder and add a column with a transformation of the nested tables.
Certainly can. Make the code in this video as a function that takes a table argument. Add a column to each file in the folder using the function then use table.combine on the new column to create a combined table
That would likely work for this particular file, but the solution was intended for more unpredictable scenarios. But I agree, in this case, your solution is easier. Thanks for watching.
Hi Brian. Thank you very much for another educative and great video. Question: PQ / M-code contains like 800 different functions. Where should I start ?? I know where I want to go and what the result of the import should be, but I have not the slightest clue which function I should use. I have studied the m code like you do in the videos - try something "automatically" and then tweak the code provided by the program but, well, I let´s say that my end results are not as good as yours. As always - thank you for your videos and dedication. I, and many more are very greatful for the time and work you spend on this.
I think we're all in the same boat, my friend. For me, it usually comes down to a LOT of Googling, experiment, and a dash of luck. With time, experience, and a TON of patience, you begin to get a feel for things. There's no quick answer to something as vast and powerful as the M language. I wish I had a better "quick fix" answer for you, but I still consider myself a "beginner" compared to some of the true "M Code Gods" out there. We'll do the best to support one another. Thanks.
Can you do a version of this if the information in a column changes position? eg. column 1 has sales next time it appears in column 2. PDF credit card statements do this.
I would assume that you would have to perform some sort of pre-test to detect the key-word in 1 of 2 columns, then perform the detection steps accordingly.
I only use Power Query for work, but I enjoy watching your videos in my free time. Could be because you are such a good story teller. 🧡
Wow, thank you! That is a great compliment. I appreciate your viewership.
I’ve viewed several tutorials regarding dynamically identifying header rows. I can tell you this, to me, is the best solution of its category. To make it complete, kindly create a function that can be applied to multiple files in a single folder at one go.
Well done.
I was going to say exactly the same!!! This is very simple and yet powerful!!
Thanks for the compliment!
I'm working on a version that will process a series of files from a folder. As soon as I have it finished, I'll be posting an update. Thanks for watching.
@@bcti-bcti transformation of a nested table? That's how I do it, I do the transformation in 1 table, then connect to the folder and add a column with a transformation of the nested tables.
I used to find Power Query quite challenging, but after watching your videos, it now feels so much easier.
@@IrfanChanna Wow! That is HIGH praise. I’m so glad to hear it has helped. Thanks for watching.
Another great video. Could you continue this in a way to combine all .csv files in the folder? So both in this particular instance.
@@michaelt312 That’s a GREAT IDEA!!! I’ll start testing first thing in the morning. I’ll let you know what I come up with. Thanks for the idea.
Certainly can. Make the code in this video as a function that takes a table argument. Add a column to each file in the folder using the function then use table.combine on the new column to create a combined table
Great tutorial as usual. Appreciate your ability to explain complex concepts in simple terms. Happy New Year!
Thank you for the kind words. Happy New Year to you, too.
Excellent dynamic solution!! Thank you very much for sharing!!
Thanks. I think it's pretty cool.
Amazing! Thanks so much!!!
Happy New Year to you and yours.
It's my pleasure! Here's to a great new year.
Yet another great video. You sir are a genius, thanks you for making learning excel this fun
"Genius"? I'll take that and put it on my resume now that I've been officially recognized as such. Thank you, kind sir. 😁
Great vid. I would just filter on say the date column to blank to keep my query dynamic.
That would likely work for this particular file, but the solution was intended for more unpredictable scenarios. But I agree, in this case, your solution is easier. Thanks for watching.
@bcti-bcti oh yeah in the context of other files, your route is much more reliable.
You're the best! Keep up the good work man!!
Thank you! I'll try my best to keep up the high expectations.
Very useful, thanks for sharing. Happy New Year!
You're welcome. Happy New Year to you, as well.
Very clear explaination.
Thank you for the kind comment. I try to be as clear as possible.
Great video as always. Thanks for sharing
My pleasure. Happy to share.
Great techniques; many thanks.
You're very welcome!
This is brilliant, Thank you very much
You're very welcome!
Good solution, great! Thanks.
You’re welcome. Thanks for watching.
Hi Brian. Thank you very much for another educative and great video.
Question: PQ / M-code contains like 800 different functions. Where should I start ?? I know where I want to go and what the result of the import should be, but I have not the slightest clue which function I should use.
I have studied the m code like you do in the videos - try something "automatically" and then tweak the code provided by the program but, well, I let´s say that my end results are not as good as yours.
As always - thank you for your videos and dedication. I, and many more are very greatful for the time and work you spend on this.
I think we're all in the same boat, my friend. For me, it usually comes down to a LOT of Googling, experiment, and a dash of luck. With time, experience, and a TON of patience, you begin to get a feel for things. There's no quick answer to something as vast and powerful as the M language. I wish I had a better "quick fix" answer for you, but I still consider myself a "beginner" compared to some of the true "M Code Gods" out there. We'll do the best to support one another. Thanks.
Well explained.
Much appreciated. I’m glad it made sense.
Can you do a version of this if the information in a column changes position? eg. column 1 has sales next time it appears in column 2. PDF credit card statements do this.
I would assume that you would have to perform some sort of pre-test to detect the key-word in 1 of 2 columns, then perform the detection steps accordingly.
@@bcti-bcti Thanks and I'll try that.
Great video. Thanks for sharing. Please can these methods also be applied to other source files like PDF
I would think this technique could be applied to any data source once the data has been brought into Power Query. Thanks for watching.
Genius, thank you!
Now it's time for YOU to look like The Genius. Thanks for watching.
Very nice tip, that you!
You're welcome!
Fantastic
Thank you! Cheers!
Prosperous 2025
@@flaviogarlatticosta Thank you. You as well.