@@utkur765 I did it the way in the video because I wanted a way that works in virtually every scenario. Your suggestion would be MUCH easier in this specific scenario but would fail if the other column didn't have the blank (null) cells to trigger from. It's good to see you thinking of a better way. Thanks for watching and contributing to the conversation.
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.
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.
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
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.
Another way to select the good range of values in one go that might please you. // Select range of values Find_Range = Table.Range(Source, List.PositionOf(Source[Column1],"Sales Representative"),List.PositionOf(Source[Column1],"Total Sales")-List.PositionOf(Source[Column1],"Sales Representative")),
I really like that solution. I'm a big fan with consolidating multiple small steps into a single, larger step. I've added that technique to my "bag of tricks". Thanks for sharing!!!
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 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.
@@bcti-bcti Hi, why don't you use second column of the source file and simply filter out all blanks? I think query will be shorter and simpler.
@@utkur765 I did it the way in the video because I wanted a way that works in virtually every scenario. Your suggestion would be MUCH easier in this specific scenario but would fail if the other column didn't have the blank (null) cells to trigger from. It's good to see you thinking of a better way. Thanks for watching and contributing to the conversation.
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.
Excellent dynamic solution!! Thank you very much for sharing!!
Thanks. I think it's pretty cool.
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.
Very clear explaination.
Thank you for the kind comment. I try to be as clear as possible.
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.
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 techniques; many thanks.
You're very welcome!
Great video as always. Thanks for sharing
My pleasure. Happy to share.
This is brilliant, Thank you very much
You're very welcome!
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
Very useful, thanks for sharing. Happy New Year!
You're welcome. Happy New Year to you, as well.
Amazing! Thanks so much!!!
Happy New Year to you and yours.
It's my pleasure! Here's to a great new year.
Good solution, great! Thanks.
You’re welcome. Thanks for watching.
Perfect solution.
Thanks. Be sure to read the comments. Many viewers are posing some FANTASTIC tips for making this even better. Thanks for watching.
Well explained.
Much appreciated. I’m glad it made sense.
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!
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.
Fantastic
Thank you! Cheers!
Another way to select the good range of values in one go that might please you.
// Select range of values
Find_Range = Table.Range(Source, List.PositionOf(Source[Column1],"Sales Representative"),List.PositionOf(Source[Column1],"Total Sales")-List.PositionOf(Source[Column1],"Sales Representative")),
I really like that solution. I'm a big fan with consolidating multiple small steps into a single, larger step. I've added that technique to my "bag of tricks". Thanks for sharing!!!
@bcti-bcti thanks for your wonderful tutorials.
@@boissierepascal5755 My pleasure. Than YOU for watching.
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.
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.
Prosperous 2025
@@flaviogarlatticosta Thank you. You as well.