📗 Free template with queries here: »» cutt.ly/up4v2108M01FD 🎯 Don't spend an age of tedious copy and paste work to combine lists when you could do this in miutes. You may not be familiar with it yet, but there's an easy way to combine multiple lists in Excel that doesn't require formulas or code! 👉 Up4Excel Downloads Library: »» ml.up4excel.com/library
14:43. first time here. Let me suggest one thing, Table.ColumnNames - it wil get names or all columns from a table. so instead of hardcoding Table column u could do it dynamicly So if u modify ur code to: let Source = Excel.CurrentWorkbook(){[Name="tblProdCats"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Region", each tblRegions), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Customer Segment", each tblCustSegs), #"Expanded Region" = Table.ExpandTableColumn(#"Added Custom1", "Region", Table.ColumnNames(tblRegions), {"Regionn"}), #"Expanded Customer Segment" = Table.ExpandTableColumn(#"Expanded Region", "Customer Segment", Table.ColumnNames(tblCustSegs), {"Customer Segment"}) in #"Expanded Customer Segment" then the column name change should not affect the result.
@@ExcelInstructor Very interesting and helpful tip Radoslaw 👍It's a fairly simple edit to the query too which I like. Thanks for watching, welcome to Up4Excel
Must credit you on your concise explanations & side notes (like where your queries & connections window may be), you must be a teacher/trainer with presentation skills like that! Its always good to pick up new efficiency tips & tricks along side the main skills the video shows.
Thanks for the compliments there 👍 I'm not a teacher by trade but all the rest of my family are or were so maybe that's where it comes from. Glad you're getting something from my videos.
Thank you so much for this video. Is that possible dynamic column number in power query ? For example only PRODUCT and CATEGORY cambination,or REGION and CATEGORY. Can we choose columns from list of column names ? Thank you again.
Power Query uses column names as references so pretty fixed in that regard. However, there are ways around it if you change the code to use variables and link them to names ranges in the spreadsheet. Way way too advanced for describing here but hopefully gives you something to Google search...or ChatGPT 👍
The technique should be exactly the same. Once you have merged 2 you can then merge one more with that output and keep repeating two at a time until all are done. Thanks for your appreciation. Good luck 👍 John
Nothing obvious comes to mind, but to follow the exact same procedure you could duplicate the queries and remove all the columns except one from each copy. For example, if a table has 3 columns, import it to power query, then copy it twice more in power query, then delete all but one column in each. You now have 3 storage queries for each item.
Dear John, this is an awesome presentation. I was wondering if it was possible to make a macro for this? Excell is getting disturbed by the changing ranges (I have many lists that need to be combined).
I'm theory you can make a macro to do anything Excel can do so I'm sure it's possible. It might be worth taking a step back and considering if combining lists is the only way to your final end point. There may be a way to get what you want without doing this 🤔
VSTACK !! Way faster if you are stscking tabkes it is fully dynamic without refresh. If you put each table on a different sheet then you can use 3D references and new sheets added with no effort.
Certainly am... there's several methods I go through and all are way way faster than doing it manually. Always happy to learn a faster method though....so send me a link to your video of one once you've done it 👍
Timings are in the description plus chapters on the video. The fast method is first up and 7 minutes starting at 1:50. It's advanced though, which is why I included the other methods. If you genuinely want to combine long lists this is 7 minutes very well spent I'd say 👍
📗 Free template with queries here: »» cutt.ly/up4v2108M01FD
🎯 Don't spend an age of tedious copy and paste work to combine lists when you could do this in miutes. You may not be familiar with it yet, but there's an easy way to combine multiple lists in Excel that doesn't require formulas or code!
👉 Up4Excel Downloads Library: »» ml.up4excel.com/library
14:43.
first time here.
Let me suggest one thing, Table.ColumnNames - it wil get names or all columns from a table. so instead of hardcoding Table column u could do it dynamicly
So if u modify ur code to:
let
Source = Excel.CurrentWorkbook(){[Name="tblProdCats"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Region", each tblRegions),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Customer Segment", each tblCustSegs),
#"Expanded Region" = Table.ExpandTableColumn(#"Added Custom1", "Region", Table.ColumnNames(tblRegions), {"Regionn"}),
#"Expanded Customer Segment" = Table.ExpandTableColumn(#"Expanded Region", "Customer Segment", Table.ColumnNames(tblCustSegs), {"Customer Segment"})
in
#"Expanded Customer Segment"
then the column name change should not affect the result.
@@ExcelInstructor Very interesting and helpful tip Radoslaw 👍It's a fairly simple edit to the query too which I like. Thanks for watching, welcome to Up4Excel
So wonderful! Your explanation is extremely easy to understand ❤
Glad you liked it and found the video useful. Thanks for the feedback 👍 John
Nice one John! Clever tricks. Thanks for demonstrating. Thumbs up!!
Cheers Wayne 👍
This is awesome, thank you
Must credit you on your concise explanations & side notes (like where your queries & connections window may be), you must be a teacher/trainer with presentation skills like that!
Its always good to pick up new efficiency tips & tricks along side the main skills the video shows.
Thanks for the compliments there 👍 I'm not a teacher by trade but all the rest of my family are or were so maybe that's where it comes from. Glad you're getting something from my videos.
Thank you so much for this video. Is that possible dynamic column number in power query ? For example only PRODUCT and CATEGORY cambination,or REGION and CATEGORY. Can we choose columns from list of column names ? Thank you again.
Power Query uses column names as references so pretty fixed in that regard. However, there are ways around it if you change the code to use variables and link them to names ranges in the spreadsheet. Way way too advanced for describing here but hopefully gives you something to Google search...or ChatGPT 👍
I really appreciate your work. Great video.
I was wondering how i can approach this if i have like 7 lists
The technique should be exactly the same. Once you have merged 2 you can then merge one more with that output and keep repeating two at a time until all are done. Thanks for your appreciation. Good luck 👍 John
Thank you so much
No problem, glad to help 👍 John
Quick question, are there any best practices when the tables have more than one column?
Nothing obvious comes to mind, but to follow the exact same procedure you could duplicate the queries and remove all the columns except one from each copy. For example, if a table has 3 columns, import it to power query, then copy it twice more in power query, then delete all but one column in each. You now have 3 storage queries for each item.
Dear John, this is an awesome presentation. I was wondering if it was possible to make a macro for this? Excell is getting disturbed by the changing ranges (I have many lists that need to be combined).
I'm theory you can make a macro to do anything Excel can do so I'm sure it's possible. It might be worth taking a step back and considering if combining lists is the only way to your final end point. There may be a way to get what you want without doing this 🤔
Nice one John. :) :)
Thanks for the comment John 👍
How do you do this on MacBook? :(
Not sure as don't have one...Google or ChatGPT??
VSTACK !! Way faster if you are stscking tabkes it is fully dynamic without refresh. If you put each table on a different sheet then you can use 3D references and new sheets added with no effort.
Great tip for 365 users Richard. VSTACK still hasn't reached me at the time of writing but looking forward to the rollout of all these new functions 👍
Hello, you still checking comments on this one? Have a question to ask.
I have just checked comments and seen this. Can't promise I can answer it but you might as well ask 👍 John
"Quickly"
"28:43"
Are you fucking serious?
Certainly am... there's several methods I go through and all are way way faster than doing it manually. Always happy to learn a faster method though....so send me a link to your video of one once you've done it 👍
@@Up4Excel Easily. Just give me the time codes of the portion of the video that gives the fastest method, and I'll make a video out of that.
Timings are in the description plus chapters on the video. The fast method is first up and 7 minutes starting at 1:50. It's advanced though, which is why I included the other methods. If you genuinely want to combine long lists this is 7 minutes very well spent I'd say 👍