Awesome Wyn! Very helpful. Love the headings trick. Thanks for hosting the challenge and also for providing the sample file to follow along. Much appreciated :)) Thumbs up!!
@@AccessAnalytic Definitely!! Only way to learn is with hands on the keyboard :)) Your challenges are great! One thought is for you to release a short YT video presenting the problem when you post the challenge on your blog. That way, YTubers get the alert before you release the solution video. Just an idea. I've seen a few others do it this way. Thanks again!!
Nice use of Table.ColumnNames! I love it. To solve your example, I would use "Unpivot other columns" that allows me to finish the task without worrying how many columns there to be unpivoted (providing that the anchor columns are constant). Then turn it into a function and apply it to all Tables.
My approach was very similar but I went the Curbal route and added the region column to the nested tables and combined the tables, rather than combining a list of tables to get the column names to use to expand. Really enjoyed the tips the last one filtering by column names was great, you could use the logic in the table.selectrows function as it delivers a true or false value it would filter directly.
I like the solution to add the custom column to check if the first column is date, but wouldn't the Consolidation table still get included as it also has Date as the first column? Or am I missing something?
Thanks Wyn - I wasn't going mad then! But it's a good way of checking that you only include the tables you need especially if you have other tables and ranges in the same workbook that you don't want to include.
Excellent video, Wyn. Do you record then edit using Camtasia? Or, what is your real-time method to zoom in and out on the fly? Keep up the good work! I learned a lot. Thanks.
Thank you sir for all your vidéos. Please how can we do if headers was written in différent way for exemple ZonesA zone a ZONE à..... thank you an advance
What about combining tabs where header names are not exactly the same? For example, on one table the column header name will be "order number" and the other column header will be "order". I essentially need to combine all tabs following the column placement, and not worrying about the header name from the other tables.
Hello, thanks for the video! Do you know of a way to be able to add a comments column to a query table and have the manually inputted comments recognise what row it's on after the refresh. So that whenever a new row is added (when new files are dropped into the source folder) the manually entered comment also shifts with that row?? I have tried creating a self reference query but it doesn't quite work
I don't think there's a reliable way of doing that other than creating some sort of ID in your data and having a separate comments table with the same ID that you merge on the side at the end.
I have 10 files each with 25 columns that have variations of column headers ..i.e. Zip, Zip Code, Postal Code, Birth Date, Day of Birth, etc. How should I implement your solution? Have columns arrange in correct order for each file, then use the correct Headings table to replace in all the files (in the raw files or Power Query)? Any advice is appreciated. Thanks
Potentially I'd unpivot the Sample File to get a column of Zip, Zip Code, Postal Code etc, then do a merge with a 2 column table that you've set up that lists "Wrong Name : Right Name".
![Felix "Unfair" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/Oswujxm2Ag0/mqdefault.jpg)
Everytime I watch your videos I end up with more work to do to update my queries... but they are running better than ever!!!
Hah! Nice one Simon 😁
@@AccessAnalytic Same here, just in time for my data cleaning project. Thanks Wyn!
Same here, just in time for my data cleaning project. Thanks Wyn!
@@txreal2 you're welcome
@@txreal2 😁
Every video you bring with new tricks.
That's really great to learn new things.
Amazing. Liked it.
Thank you. The kind comments are greatly appreciated!
Love this dynamic col approach to a common problem
Cheers John
You've push it to another level again, Wyn. I find it very useful especially where we can not control the table content.
Thanks for watching and commenting Cy
Another great challence and wonderful solution!
Thanks for sharing your knowledge and appreciation for Power Query, Wyn
Thanks Melissa
Awesome Wyn! Very helpful. Love the headings trick. Thanks for hosting the challenge and also for providing the sample file to follow along. Much appreciated :)) Thumbs up!!
Thanks Wayne, glad you're following along 👍
@@AccessAnalytic Definitely!! Only way to learn is with hands on the keyboard :)) Your challenges are great! One thought is for you to release a short YT video presenting the problem when you post the challenge on your blog. That way, YTubers get the alert before you release the solution video. Just an idea. I've seen a few others do it this way. Thanks again!!
Great stuff Wyn as always. That column tip is very timely for something I'm just working on!
Great to know it's helped you Shirley, thanks for letting me know 😃
Nice use of Table.ColumnNames! I love it.
To solve your example, I would use "Unpivot other columns" that allows me to finish the task without worrying how many columns there to be unpivoted (providing that the anchor columns are constant). Then turn it into a function and apply it to all Tables.
Nice, I go to functions if I can’t do it without them, I find it’s easier to maintain and explain to others
Absolutely awesome Wyn, really love this approach. I have bookmarked this to the top of my useful techniques for Power Query 😊
That’s great Ivan.
My approach was very similar but I went the Curbal route and added the region column to the nested tables and combined the tables, rather than combining a list of tables to get the column names to use to expand. Really enjoyed the tips the last one filtering by column names was great, you could use the logic in the table.selectrows function as it delivers a true or false value it would filter directly.
Good stuff Glyn
👍👌very helpful this is what I was searching for😉
Cheers
Great video Wyn, lots of great techniques. 👍
Cheers
Excellent thanks Wyn.
No worries Martin, you're welcome
Amazing video!
Thank you fizex
Awesome Wyn, I’ll be using this for sure 👍
Nice one Brad
That was amazing!
Cheers Zach!
Thank you and much appreciated for your valuable informations ,,,
You're welcome Mohammed
Brilliant. Thanks WYN
Thanks Dave
Wow. This Table.ColumnName function is very handy.
It certainly is Khawar
Very useful. Thanks, Wyn.
No worries Ian
Thanks! This is very nice!
Cheers
Great video, thanks
Thanks for taking the time to leave a kind comment
Great content🙌🔥🙏
Cheers
I like the solution to add the custom column to check if the first column is date, but wouldn't the Consolidation table still get included as it also has Date as the first column? Or am I missing something?
Yes, good point Shirley, this would need to be done be in addition to filtering out your consolidation table
Thanks Wyn - I wasn't going mad then! But it's a good way of checking that you only include the tables you need especially if you have other tables and ranges in the same workbook that you don't want to include.
Absolutely @@shirleymoreman6725
Excellent video, Wyn. Do you record then edit using Camtasia? Or, what is your real-time method to zoom in and out on the fly? Keep up the good work! I learned a lot. Thanks.
Hi, glad the videos are helping you. Yes I use Camtasia and do all my zooming etc there.
Thank you sir for all your vidéos. Please how can we do if headers was written in différent way for exemple ZonesA zone a ZONE à..... thank you an advance
Does this help ruclips.net/video/09tvia_8ykI/видео.html
Great video.
Can you share the link of previous video wherein you had dealt with dynamic headings?
Thanks , here you go
How to use Power Query to Combine Multiple Files that have different headings
ruclips.net/video/09tvia_8ykI/видео.html
@@AccessAnalytic Thanks a lot !
You’re welcome
Brilliant
Cheers Justin
What about combining tabs where header names are not exactly the same? For example, on one table the column header name will be "order number" and the other column header will be "order". I essentially need to combine all tabs following the column placement, and not worrying about the header name from the other tables.
You could demote headers and remove first row in the transform step
At time marker 2:50, you mentioned another video you did regarding avoiding referring to headings. Can you point me to that video please?
@rfigueroa76 might be this one ruclips.net/video/09tvia_8ykI/видео.htmlsi=BKYQBsJ_klSOzATU
Do you have a way of combining this with the different headings video? I have a similar situation with the addition that it's all on SharePoint
This maybe: ruclips.net/video/09tvia_8ykI/видео.html
Hello, thanks for the video! Do you know of a way to be able to add a comments column to a query table and have the manually inputted comments recognise what row it's on after the refresh. So that whenever a new row is added (when new files are dropped into the source folder) the manually entered comment also shifts with that row??
I have tried creating a self reference query but it doesn't quite work
I don't think there's a reliable way of doing that other than creating some sort of ID in your data and having a separate comments table with the same ID that you merge on the side at the end.
Maybe this but I've never tried myself exceleratorbi.com.au/self-referencing-tables-power-query/
@@AccessAnalytic thanks for this Wyn. I have my own workaround but it is not ideal. Matt's solution might work for me.
No worries
Brillant !!!
Thanks!
Very cool - now do it in Power BI 🙃
Should work the same. Just point to Excel Workbook.
Sorry I meant with a file in sharepoint@@AccessAnalytic
I have 10 files each with 25 columns that have variations of column headers ..i.e. Zip, Zip Code, Postal Code, Birth Date, Day of Birth, etc.
How should I implement your solution?
Have columns arrange in correct order for each file, then use the correct Headings table to replace in all the files (in the raw files or Power Query)?
Any advice is appreciated. Thanks
Potentially I'd unpivot the Sample File to get a column of Zip, Zip Code, Postal Code etc, then do a merge with a 2 column table that you've set up that lists "Wrong Name : Right Name".
sir how to contact you
Wyn.bio.link