Combining Excel Tables in a highly flexible way

Рет қаралды 13,654

Күн бұрын

Power Query is awesome at consolidating Excel data, and with a couple of tricks you can make it even more flexible and less likely to error out.
Here's a link to the challenge and the file if you want to have a go before or after watching my solution
accessanalytic.com.au/power-o...
00:00 Intro
01:47 Excel.CurrentWorkbook()
03:10 Creating a Headings Step
05:11 Replace Expanded List with dynamic headings step
05:28 Replacing nulls to keep items when unpivotting
07:22 Sorting Columns
07:58 THE TRAP!
10:51 Testing it all works...
Thanks everyone!
Connect with me on LinkedIn and Twitter via
wyn.bio.link/

Пікірлер: 85

@mrsimonexeter 2 жыл бұрын

Everytime I watch your videos I end up with more work to do to update my queries... but they are running better than ever!!!

@AccessAnalytic 2 жыл бұрын

Hah! Nice one Simon 😁

@txreal2 2 жыл бұрын

@@AccessAnalytic Same here, just in time for my data cleaning project. Thanks Wyn!

@txreal2 2 жыл бұрын

Same here, just in time for my data cleaning project. Thanks Wyn!

@AccessAnalytic 2 жыл бұрын

@@txreal2 you're welcome

@AccessAnalytic 2 жыл бұрын

@@txreal2 😁

@syedaneesdurez8766 Жыл бұрын

Every video you bring with new tricks. That's really great to learn new things. Amazing. Liked it.

@AccessAnalytic Жыл бұрын

Thank you. The kind comments are greatly appreciated!

@ivangardiner66 2 жыл бұрын

Absolutely awesome Wyn, really love this approach. I have bookmarked this to the top of my useful techniques for Power Query 😊

@AccessAnalytic 2 жыл бұрын

That’s great Ivan.

@melissa3558 2 жыл бұрын

Another great challence and wonderful solution! Thanks for sharing your knowledge and appreciation for Power Query, Wyn

@AccessAnalytic 2 жыл бұрын

Thanks Melissa

@shirleymoreman6725 2 жыл бұрын

Great stuff Wyn as always. That column tip is very timely for something I'm just working on!

@AccessAnalytic 2 жыл бұрын

Great to know it's helped you Shirley, thanks for letting me know 😃

@ExcelOffTheGrid 2 жыл бұрын

Great video Wyn, lots of great techniques. 👍

@AccessAnalytic 2 жыл бұрын

Cheers

@CyPersia 2 жыл бұрын

You've push it to another level again, Wyn. I find it very useful especially where we can not control the table content.

@AccessAnalytic 2 жыл бұрын

Thanks for watching and commenting Cy

@martyc5674 2 жыл бұрын

Excellent thanks Wyn.

@AccessAnalytic 2 жыл бұрын

No worries Martin, you're welcome

@wayneedmondson1065 2 жыл бұрын

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 2 жыл бұрын

Thanks Wayne, glad you're following along 👍

@wayneedmondson1065 2 жыл бұрын

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

@bradfry6396 2 жыл бұрын

Awesome Wyn, I’ll be using this for sure 👍

@AccessAnalytic 2 жыл бұрын

Nice one Brad

@johnhackwood1568 Жыл бұрын

Love this dynamic col approach to a common problem

@AccessAnalytic Жыл бұрын

Cheers John

@abdullah_alhathloul_ 2 жыл бұрын

Great video, thanks

@AccessAnalytic 2 жыл бұрын

Thanks for taking the time to leave a kind comment

@iankr 2 жыл бұрын

Very useful. Thanks, Wyn.

@AccessAnalytic 2 жыл бұрын

No worries Ian

@davecope3322 2 жыл бұрын

Brilliant. Thanks WYN

@AccessAnalytic 2 жыл бұрын

Thanks Dave

@fizexbakar1986 2 жыл бұрын

Amazing video!

@AccessAnalytic 2 жыл бұрын

Thank you fizex

@ohdjrp4 2 жыл бұрын

Thanks! This is very nice!

@AccessAnalytic 2 жыл бұрын

Cheers

@teaking1981 2 жыл бұрын

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.

@AccessAnalytic 2 жыл бұрын

Good stuff Glyn

@mohammedelsakally540 2 жыл бұрын

Thank you and much appreciated for your valuable informations ,,,

@AccessAnalytic 2 жыл бұрын

You're welcome Mohammed

@brij26579 2 жыл бұрын

👍👌very helpful this is what I was searching for😉

@AccessAnalytic 2 жыл бұрын

Cheers

@FRANKWHITE1996 2 жыл бұрын

Great content🙌🔥🙏

@AccessAnalytic 2 жыл бұрын

Cheers

@zachg.9208 2 жыл бұрын

That was amazing!

@AccessAnalytic 2 жыл бұрын

Cheers Zach!

@wmfexcel 2 жыл бұрын

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.

@AccessAnalytic 2 жыл бұрын

Nice, I go to functions if I can’t do it without them, I find it’s easier to maintain and explain to others

@khawarmalik5239 2 жыл бұрын

Wow. This Table.ColumnName function is very handy.

@AccessAnalytic 2 жыл бұрын

It certainly is Khawar

@boissierepascal5755 Жыл бұрын

Brillant !!!

@AccessAnalytic Жыл бұрын

Thanks!

@justinbarter5445 Жыл бұрын

Brilliant

@AccessAnalytic Жыл бұрын

Cheers Justin

@10ozGold 2 жыл бұрын

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.

@AccessAnalytic 2 жыл бұрын

Hi, glad the videos are helping you. Yes I use Camtasia and do all my zooming etc there.

@cheikhhakim5864 Жыл бұрын

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

@AccessAnalytic Жыл бұрын

Does this help kzfaq.info/get/bejne/Zp-kqcySxJ7dm30.html

@SamsungA-wv5bh 2 жыл бұрын

Great video. Can you share the link of previous video wherein you had dealt with dynamic headings?

@AccessAnalytic 2 жыл бұрын

Thanks , here you go How to use Power Query to Combine Multiple Files that have different headings kzfaq.info/get/bejne/Zp-kqcySxJ7dm30.html

@SamsungA-wv5bh 2 жыл бұрын

@@AccessAnalytic Thanks a lot !

@AccessAnalytic 2 жыл бұрын

You’re welcome

@gh7769 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

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.

@AccessAnalytic 2 жыл бұрын

Maybe this but I've never tried myself exceleratorbi.com.au/self-referencing-tables-power-query/

@GrainneDuggan_Excel 2 жыл бұрын

@@AccessAnalytic thanks for this Wyn. I have my own workaround but it is not ideal. Matt's solution might work for me.

@AccessAnalytic 2 жыл бұрын

No worries

@tubeampsrule1 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

This maybe: kzfaq.info/get/bejne/Zp-kqcySxJ7dm30.html

@user-mu5ku9ev2s 11 ай бұрын

Very cool - now do it in Power BI 🙃

@AccessAnalytic 11 ай бұрын

Should work the same. Just point to Excel Workbook.

@user-mu5ku9ev2s 11 ай бұрын

Sorry I meant with a file in sharepoint@@AccessAnalytic

@shirleymoreman6725 2 жыл бұрын

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?

@AccessAnalytic 2 жыл бұрын

Yes, good point Shirley, this would need to be done be in addition to filtering out your consolidation table

@shirleymoreman6725 2 жыл бұрын

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.

@AccessAnalytic 2 жыл бұрын

Absolutely @@shirleymoreman6725

@txreal2 2 жыл бұрын

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

@AccessAnalytic 2 жыл бұрын

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

@rfigueroa76 7 ай бұрын

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.

@AccessAnalytic 7 ай бұрын

You could demote headers and remove first row in the transform step

@rfigueroa76 7 ай бұрын

At time marker 2:50, you mentioned another video you did regarding avoiding referring to headings. Can you point me to that video please?

@AccessAnalytic 7 ай бұрын

@rfigueroa76 might be this one kzfaq.info/get/bejne/Zp-kqcySxJ7dm30.htmlsi=BKYQBsJ_klSOzATU