Great tutorial, thanks Mike. My was different on Excel 2016; I did not have Get Data in the ribbon nor was it available by customizing the ribbon. There was a Combine menu under the ribbon which I hit - Merge & Combine became available however these they were greyed out. What worked for me was to right-click in the Query Pane on a table and selected Append from there, hopefully this helps someone some day.
Nice way to work with very long tables! Also in excel 2010 using powerquery you can create a pivot table from query connection only by choosing "Use an external data source" in the create PivotTabe dialogue without adding the table to the data model (there is no data model in excel 2010), so you can append large tables together in powerquery and summarize in a pivottable, but of course you will not be able to create measures other than what is available in the pivottable by default
Awesome Sir...Thank you very much ....One doubt....After adding two tables to Data model and Pivot Table , now I have 3rd Table how do I add that table to same Data model and update Pivot Table...
Interesting to see the filesize shrink considerably. I assume thats because Excel only store the data, and not all the cell metadata for formatting, positioning etc. Great Video :)
Thanks for another awesome video. Question, please-- Why can't I insert a function after the combined pivot table is built? For example, I need to apply a percentage to one column in the table but the option isn't available .
Glad it is great and helped you, Maxime!!!! Yes, there are many videos with many topics, just search the channel to find what you need. If you need help finding something, just ask me : )
@@excelisfun Actually yes, I have a case just like yours and I made a pivot bar chart combining both sets of data. Is there a way to highlight one set of data in the chart ? (For instance, you have $800 sales in January => $200 from first connection table, $600 from second connection table => Can we highlight one of these two values on the main chart?) Seems tricky right! Could be a future video tuto maybe :) (also calculated fields seem disable with the 'data model' method.. huh)
Hi! Thank you so much for the video, it was very informative! When I tried to delete the tables after I have appended them and loaded to data model, the data connect shows an error message that the table is not found. Can I check how I am able to remove this problem? Thank you!
Great Video. Just one question. Is there a way to use getpivot and get data out of this combined database in other sheet or workbook? Doesnt seem to be working.
This is exactly what I was looking for. We have 12 large tables that need to be merged. However, our office still has MS-Office 2013. How can I do this using Excel 2013?
Thank you for the help! I had one question, the last step doesn't quite work for me as an unexpected error occurs. After doing the step showed in 6:12, I do not see a PivotTable sidebar with fields, how can I do so? Thanks!
@@excelisfun the PivotTable isn't being created after clicking on "OK" which is why the Analyze tab doesn't appear either. Thus, I'm unable to see the "Show Field List" option when right clicking
Hi, great video!! I have one question, when I remove the table to make the file smaller, I can´t refresh anymore since it says that it can´t find the Table. I have made sure to add it to the data model, but is there something else I am missing?
You can't refresh with this particular Trick. It would be better to keep Workbook as a separate file, then open a new workbook, and use the Get Data, File, Excel File option and then import that way.
@@excelisfun I was planning on using this approach with my financial statement reporting. I have a full year from last fiscal year, but I'm only in month 7 of my current fiscal year. For me to better understand your reply to Helena, this is what I am understanding. I will have one workbook that will have the source tables, and another workbook that connects to the tables, and that can have the subsequent tabs for my reporting. Correct?
This feature seems to be exactly what I was looking for. My data sets are updated weekly is there a good way to update data tables once they have been uploaded and combined?
Maybe videos #2, 3 or 5 in this playlist: ruclips.net/p/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK Pleas help this free Excel resource excelisfun channel with Thumbs Ups on each vid and a Sub : )
Relationships are different than Append. Relationships are when you have a Lookup or Dimension Tables that have items you need to lookup or attributes that you need to use as criteria / filters for reports usually based on the Sales / Fact Table. Append is when you take to parts of a single table and make it a single table. Append in when you have the same extact columns in both and you want to combine into a single table.
Just in Excel 2016. It is just a great feature. I use it all the time home where I do not have the right version with Power Pivot. There are a few necessary things that we can't do though, like Calculated Columns, Sort by Column and a few other things.
sir, thank you for the vid. it's sure an learning. my query is, why can't we take unique and use sumif formula which will be more easier and simple and powerful I believe. please clarify sir. thanking you once again. -Rakesh
Hi Sir and all the viewer, Hope you all are doing well !! I have one query related with with survey 1 video. If in excel data is mentioned like below 1 A 2 B 3 C 4 A 5 B 6 C 7 A 8 B 9 C ...........till 99 Then how to get data like below in excel or which formula can help us to get below format data A B C 1 2 3 4 5 6 7 8 9 ........like that.... Could you please help on this ASAP if possible? Regards, Anand
I have been looking for a way to do this for 3 days, finally found your video and it immediately worked. Thank you so much!
Glad the video can help you, Paul!
This is amazing! The small file size, and the ability to put that massive info behind the scenes. YEAH! This is cool.
Glad you like it : )
Excellent, thank you sòooooooo much , dug me out of a whole and actually learnt something.
Yes!!!!!
This helped A LOT!! Thank you for posting and doing what you do!!!
You are welcome! Thanks for your support : )
Thanks for your video! It helped me a lot
Life saver! Great tutorial!
Great tutorial, thanks Mike. My was different on Excel 2016; I did not have Get Data in the ribbon nor was it available by customizing the ribbon. There was a Combine menu under the ribbon which I hit - Merge & Combine became available however these they were greyed out.
What worked for me was to right-click in the Query Pane on a table and selected Append from there, hopefully this helps someone some day.
That's really amazing and helpful video, Thanks a lot
Nice thanks for this was having trouble with my file sizes too so double tips :)
Thanks a ton.... Really it is awesome...!!!
You are welcome a ton, Shyam!!! Thanks for your support, Shyam Kumar, with your comments, thumbs ups and of course your Sub : )
Nice video... really helped
Glad it helps!
Nice way to work with very long tables!
Also in excel 2010 using powerquery you can create a pivot table from query connection only by choosing "Use an external data source" in the create PivotTabe dialogue without adding the table to the data model (there is no data model in excel 2010), so you can append large tables together in powerquery and summarize in a pivottable, but of course you will not be able to create measures other than what is available in the pivottable by default
Nice!!!
Very helpful, thanks
Glad it helps!!!
Thank you, This worked perfect I only had one problem. how do I change the Value from count to Sum. It would not let me change it.
Thank you very much...
Many, many thanks Mike!
Many you are welcomes!!!!
Really helpful. THanks a lot!
You are welcome a lot, John Yin!!! Thanks for your support with your comment, thumbs up and Sub : )
Awesome Sir...Thank you very much ....One doubt....After adding two tables to Data model and Pivot Table , now I have 3rd Table how do I add that table to same Data model and update Pivot Table...
Interesting to see the filesize shrink considerably. I assume thats because Excel only store the data, and not all the cell metadata for formatting, positioning etc. Great Video :)
No it is because of the columnar Database, built especially to compact big data.
Thanks for another awesome video. Question, please-- Why can't I insert a function after the combined pivot table is built? For example, I need to apply a percentage to one column in the table but the option isn't available .
This is helpful somehow, how about data with a different header, only one can be similar :(. Is there anyway can combine this?
Great! I tried a different tuto from 2013 and it kind of screwed the date/month organization. This is perfect, your chanel is very valuable!
Glad it is great and helped you, Maxime!!!! Yes, there are many videos with many topics, just search the channel to find what you need. If you need help finding something, just ask me : )
@@excelisfun Actually yes, I have a case just like yours and I made a pivot bar chart combining both sets of data. Is there a way to highlight one set of data in the chart ? (For instance, you have $800 sales in January => $200 from first connection table, $600 from second connection table => Can we highlight one of these two values on the main chart?) Seems tricky right! Could be a future video tuto maybe :) (also calculated fields seem disable with the 'data model' method.. huh)
Hi! Thank you so much for the video, it was very informative! When I tried to delete the tables after I have appended them and loaded to data model, the data connect shows an error message that the table is not found. Can I check how I am able to remove this problem? Thank you!
exactly what I was looking for.
how many(max) tables can I append? I will be working on at least 52tables, each with >100k rows.
Thanks.
You are welcome!
Mike, excelente. Obrigado.
Glad you like it!
Great video, thanks
Great Video. Just one question. Is there a way to use getpivot and get data out of this combined database in other sheet or workbook? Doesnt seem to be working.
Worked for me
This is exactly what I was looking for. We have 12 large tables that need to be merged. However, our office still has MS-Office 2013. How can I do this using Excel 2013?
Thank you for the help! I had one question, the last step doesn't quite work for me as an unexpected error occurs. After doing the step showed in 6:12, I do not see a PivotTable sidebar with fields, how can I do so? Thanks!
Maybe try: in the worksheet where the PivotTable should be, or is, right-click and click on "Show Field List".
@@excelisfun the PivotTable isn't being created after clicking on "OK" which is why the Analyze tab doesn't appear either. Thus, I'm unable to see the "Show Field List" option when right clicking
Hi, great video!! I have one question, when I remove the table to make the file smaller, I can´t refresh anymore since it says that it can´t find the Table. I have made sure to add it to the data model, but is there something else I am missing?
You can't refresh with this particular Trick. It would be better to keep Workbook as a separate file, then open a new workbook, and use the Get Data, File, Excel File option and then import that way.
@@excelisfun I was planning on using this approach with my financial statement reporting. I have a full year from last fiscal year, but I'm only in month 7 of my current fiscal year. For me to better understand your reply to Helena, this is what I am understanding. I will have one workbook that will have the source tables, and another workbook that connects to the tables, and that can have the subsequent tabs for my reporting. Correct?
Thumbs up!
Thank you!
Wonderful! Thanks Mike :-)
You are welcome!
Mike, nice trick
Glad you like it : )
This feature seems to be exactly what I was looking for. My data sets are updated weekly is there a good way to update data tables once they have been uploaded and combined?
Maybe videos #2, 3 or 5 in this playlist: ruclips.net/p/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK
Pleas help this free Excel resource excelisfun channel with Thumbs Ups on each vid and a Sub : )
Wouldn't it be faster to do this via Relationships in the data tab? Also, does this data model work for tables in different workbooks?
Relationships are different than Append. Relationships are when you have a Lookup or Dimension Tables that have items you need to lookup or attributes that you need to use as criteria / filters for reports usually based on the Sales / Fact Table. Append is when you take to parts of a single table and make it a single table. Append in when you have the same extact columns in both and you want to combine into a single table.
You can still write measures even without Power Pivot. Or is that just doable in Excel 2016?
Just in Excel 2016. It is just a great feature. I use it all the time home where I do not have the right version with Power Pivot. There are a few necessary things that we can't do though, like Calculated Columns, Sort by Column and a few other things.
ليت مثل هالفيديوهات تترجم للعربية 😢
sir, thank you for the vid. it's sure an learning. my query is, why can't we take unique and use sumif formula which will be more easier and simple and powerful I believe.
please clarify sir. thanking you once again.
-Rakesh
WHOA!
wow, Data Model combined with Power Query is amazing! Thanks Mike! BTW, what exact version of Excel 2016 do you have? thanks
Excel 2016, Office 365, Pro Plus, Insider Edition, slow.
Thank you Mike
Hi Sir and all the viewer,
Hope you all are doing well !!
I have one query related with with survey 1 video.
If in excel data is mentioned like below
1 A
2 B
3 C
4 A
5 B
6 C
7 A
8 B
9 C
...........till 99
Then how to get data like below in excel or which formula can help us to get below format data
A B C
1 2 3
4 5 6
7 8 9
........like that....
Could you please help on this ASAP if possible?
Regards,
Anand
WTF I cant save the file anymore