Thanks Doug! Your videos are very helpful!

@DougHExcel

8 жыл бұрын

Glad you liked them!

Your tutorial was a tremendous help. Thank you for posting

@DougHExcel

6 жыл бұрын

glad it helped!

Thanks Doug. for another great tip!

@DougHExcel

8 жыл бұрын

You're welcome, glad you liked!

Thank you for this - it has saved my day!

@DougHExcel

5 жыл бұрын

Thanks Michael Lobosco, glad it helped!

Great video, your videos are more helpful than other one, please continue. Thanks

@DougHExcel

8 жыл бұрын

Thanks for the kind words!

Thank you so much! I have search all over the internet and when I found your video.. saved my day!

@DougHExcel

5 жыл бұрын

Hi Marlene Lundgren, glad you liked it, thanks for commenting!

finally a tutorial I can understand.. Thank you!

@DougHExcel

5 жыл бұрын

Hi Ross Castilla, glad you liked it, thanks for commenting!

Doug, I have been searching for this explanation for some time. Thank you. This will help my team achieve new goals!

@DougHExcel

Жыл бұрын

Thanks David Burton, glad it helped!

Useful, very useful. Thanks Doug H

@DougHExcel

8 жыл бұрын

Glad you found this useful!

Hey Doug. Thank you very much for this explanation. Greetings from France

@DougHExcel

5 жыл бұрын

Hi anne kaur, merci! Glad you liked it, thanks for commenting!

Gracias Dough! Un video muy útil y explicado de una manera muy simple.

@DougHExcel

3 жыл бұрын

Gracias por el comentario

Fantastic!! You saved my day. Keep it up!

@DougHExcel

5 жыл бұрын

Thanks for the comment

Excellent! Just what I needed. I have a worksheet for each bank statement. This allows me to consolidate them all and produce reports. I can add new transactions to the tables each month and I simply refresh the query. Marvelous job Doug. I would never have worked this out myself. 2016 looks slightly different (a little easier in 2016) but the idea is the same.

@DougHExcel

5 жыл бұрын

Hi Jamaican Treasures, glad you liked it, thanks for commenting!

Very useful and addressed my problem perfectly!

@DougHExcel

7 жыл бұрын

Hi Said Elnaffar, glad you liked it, thanks for commenting!

WOW, Nice! made my day!!! thanks

@DougHExcel

4 жыл бұрын

Hi Jordan Waldner...you're welcome, glad you liked!

Thanks!! It helps!!

@DougHExcel

6 жыл бұрын

Thanks Wilson Ho, glad it helped!

Flawless, - thanks for sharing!

@DougHExcel

2 жыл бұрын

Thanks for the comment!

tks 4 keeping it simple

@DougHExcel

8 жыл бұрын

You're welcome!

Fantastic. Thanks.

@DougHExcel

6 жыл бұрын

You're Welcome!

Thanks for this video learning clips

@DougHExcel

7 жыл бұрын

Hi Iqbal Hossain...you're welcome, glad you liked!

Many Thanks - the tutorial was really helpful .

@DougHExcel

6 жыл бұрын

Glad it helped!

Very useful, thank you so much.

@DougHExcel

5 жыл бұрын

Hi Michael Murphy, glad you liked it, thanks for commenting!

Thx. Just needed this one. Keep up the good work! Subscribed to you channel ;-)

@DougHExcel

Жыл бұрын

Thanks for the sub!

Good work!!! Thanks!!

@DougHExcel

5 жыл бұрын

Hi Graziano Locatelli...you're welcome, glad you liked!

This video came to rescue me for this problem to give exactly what I was looking for. Thanks a ton, I mean it. Looked everywhere and they all were consolidating the data instead of just appending, I found this video after wasting almost ~20hrs. It was right in front of me, just thought that it will another replica of what others are explaining.

@DougHExcel

5 жыл бұрын

Thanks Ankit Moral, glad it helped!

@alisonboughey4068

5 жыл бұрын

Same!

@DougHExcel

5 жыл бұрын

Hi Alison Boughey, thanks for the comment!

Awesome mate.

@DougHExcel

6 жыл бұрын

Hi Joseph Politini, thanks for the comment!

I have been struggling to make tables that have duplicate values into a seamless list that combines ingredient and amounts into one shopping list. This video finally answered all my questions and I have a working system in place! Thank you ever so much for this.

@DougHExcel

6 жыл бұрын

Glad this helped!

This is very interesting feature. Thank you on you sharing. /Watthanapong S.

@DougHExcel

6 жыл бұрын

You're Welcome!

Thank You!!

@DougHExcel

4 жыл бұрын

You’re welcome!

Thanks for getting this video

@DougHExcel

3 жыл бұрын

You bet!

THANK YOU

@DougHExcel

5 жыл бұрын

You're Welcome!

Excellent tutorial I ever saw

@DougHExcel

6 жыл бұрын

Hi Sarvani Katuri, thanks for the comment!

very nice!!!!!!

@DougHExcel

8 жыл бұрын

Thanks, glad you liked it!!

Nice and simple

@DougHExcel

4 жыл бұрын

Thanks for the comment!

Nice way to understand..

@DougHExcel

3 жыл бұрын

Hi sachin gulati, thanks for the comment!

very nice video

@DougHExcel

4 жыл бұрын

Thanks

Hi Doug, thanks for the video. Is it possible to select all tables and append all in one go rather than adding them one by one? Also, making a table for each sheet and naming it would take a long time when you have 20 or so sheets. is there a faster way?

@DougHExcel

7 жыл бұрын

I've found now that I can append multiple sheets at once instead of one-by-one. I think I may have had an earlier version of PQ or I just neglected to pursue it further in the video.

@dcfrank9326

5 жыл бұрын

@@DougHExcel How do you do this? Thanks.

Hi .. Nice video doug.. Is it mandatory to have all the column header of different tables as same ?? if yes.. how to deal such tables ?

@DougHExcel

6 жыл бұрын

It should be the same header name. You can either change it before you bring it into PowerQuery or edit it in the Power Query editor when it's brought in before appending.

Thanks for the tuts, how if each sheet have some same data for example ID and name, with different value in different column for each sheet(the value is not duplicated, just the ID)? Can power query combine them too?

@deepakvats7636

4 жыл бұрын

yes

@DougHExcel

3 жыл бұрын

Hi Yosua Wisnu, thanks for the comment!

do you any helpful videos about web query and being able to search from a web query if we have a lot of input data

@DougHExcel

7 жыл бұрын

Power Query should help with this see another video that might help kzread.info/dash/bejne/aWVmz7hymtqzfNI.html I

Do you know if individual queries can be modified (e.g. add a column to those) and then appended? I've tried to do that and the new columns added don't show up in the appended table.

@DougHExcel

6 жыл бұрын

Appending in powerquery needs the column to be the same. You may consider merging. See this video kzread.info/dash/bejne/eaCBqKSHgbi3gZs.html

Hi Doug, what if i have different types of data, such as Percentages and currency? is there a way to keep those formats in one combined list?

@DougHExcel

5 жыл бұрын

Yes usually Power Query is "smart" enough to figure out your data type. If it doesn't when you bring it into to Query window, you may need to validate/update the data type (one way is to click the left icon on that specific column header to choose the data type) before close/load.

Hi Can we make it same way pasting it column wise to compare across north, south, west & east?

@DougHExcel

6 жыл бұрын

maybe inserting a transpose step. Although this video is one another topic it does cover the transpose step a bit kzread.info/dash/bejne/hoqe1dl_j6uxc6g.html

This works great. But what if your tables are in different files? The table headers are identical in all tables but they're in different files. Can they be combined using power query?

@DougHExcel

7 жыл бұрын

Maybe this might give some insights kzread.info/dash/bejne/q4Z7kq-ilpu7j5M.html

@emwdava

7 жыл бұрын

Thanks, I'm gonna give that a go.

@DanielLamarche

6 жыл бұрын

Hello. Doug made a video titled: Combine Excel Files with Power Query that answers this question. G'Day.

nice!

@DougHExcel

3 жыл бұрын

Thanks!

Great video. Can i ask, if i want to add more worksheets, will i have to go to powerpivot option and append the new worksheet/table? Or will add the new sheet automatically? Thanks

@DougHExcel

4 жыл бұрын

With these steps, you'll need to do repeat the process as described in the video....BUT you can automate this..there are different ways and here are some options depending if it's CSV or EXCEL. kzread.info/dash/bejne/rI6sl7hrgrPaiZs.html kzread.info/dash/bejne/q4Z7kq-ilpu7j5M.html

Doug. Good information. Do you have a video that talks about similar to this, but in sentences. I mean, if the 1st row has a list of 5 options, and you click on one options, it will sends you to a 2nd column to only choose options from the 2nd column, and once you choose an item from that 2nd, it will give you , a lesser option to choose from from the 3rd column. and so on and on. Does my question make sense ? Thank you.

@DougHExcel

7 жыл бұрын

Like a dynamic list? Try this kzread.info/dash/bejne/eZWEtZJ-obm0erw.html

If the original tables have specific font formatting, can these be brought into the PQ?

@DougHExcel

5 жыл бұрын

Not that I know of...:-(

Hi Doug, I want to append 4 sheets with ~million rows each; will this still work well (as the max rows in excel can go up to ~1.1 million)? Also, can I create a Pivot Table out of the appended data? Thanks in Advance, and have a nice day!

@DougHExcel

5 жыл бұрын

Yes this can work in power query, just include it in the data model and pull it in from there to the pivot table

Thanks. A little tip for those with Office 2016+ and corporate Office 365, the Power Query is included inside Data tab. You don't need to install anything.

@DougHExcel

6 жыл бұрын

Hi Thiago Reale, thanks for the comment!

@thiagoreale

6 жыл бұрын

One question: If a new region is added in the middle of a specific year, how can we consolidate data at year level in order to consider data only since that specific day and do not put 0 on the previews dates? The problem is that if a new region appears, how can I do the correct roll up for year roll up, in a daily data, for example.

Super

@DougHExcel

4 жыл бұрын

Hi paramesh t, thanks for the comment!

Hi Doug can you share the files you've use in this video? Thanks

@DougHExcel

4 жыл бұрын

Hi InggoDC, sorry no files available :-(

Does anyone know how to do the same thing in Excel for Mac? It doesn't have the same query functionality.

@DougHExcel

4 жыл бұрын

Depends on your version. See techcommunity.microsoft.com/t5/excel-blog/get-amp-transform-power-query-in-excel-for-mac-phase-1/ba-p/876840

Excel 2010 or other version? Tks!

@DougHExcel

7 жыл бұрын

2010. Power Query is an add on for 2010 and 2013.

Is that different ahtan Power Pivot?

@DougHExcel

7 жыл бұрын

think of Power Pivot as an analysis tool and Power Query as a data clean up tool, even though both have capabilities to do similar things

What does the item under col A mean? 10K records under row 12, what does that mean, number of rows or number of data field? within that particular table W, row 12?

"automagically"... lol... reminds me of a comment a server at a restaurant made to me when I pointed out an error on the bill... She said, "I'm no mathmagician..."

@DougHExcel

5 жыл бұрын

😆

Hi i have 87 sheets..formatting them as tables should take some time..is there a way to do that?

@DougHExcel

3 жыл бұрын

maybe one of the others ones in the playlist might give some idea kzread.info/head/PL-n8f1cY_Qw9qvGvOfSfgDsoOIHxHYF1T

What will it be if the total amount of data row greater than the limit of the Excel sheet??

@DanielLamarche

6 жыл бұрын

Probably Access would be better. Then use a UNION query to combine. BTW Microsoft say that the number of sheets is: "Limited by available memory" so no worries.

@DougHExcel

3 жыл бұрын

Hi LucyFire7, thanks for the comment! Though the excel sheet as a source is constrained by row limits, if the source become a txt or csv file, the records can be much more and PQ will take that in.

@LucyFire7

3 жыл бұрын

@@DougHExcel Thanks for your reply, its very helpful ;)

Was it easier to Select All/Copy/Paste contents from other tables to first one?

@DougHExcel

6 жыл бұрын

If this is a one time operation probably...but if those tables are something that gets updated periodically and the consolidation is done on a recurring basis...PQ makes it easier.

And what to do if you have 150 sheets (1 table in each)? It will take all day!

@1Roso

5 жыл бұрын

Exactly, I thought this Power Query will be powerfull, but it is not, it is soooooo slow when have source sheet ~500 rows of data and have ~33 queries (using reference, because when new queries have been used, my 8GB station was not enough for excel to calculate them... it just froze :( )

@DougHExcel

3 жыл бұрын

7absinth, that is very interesting...thanks for letting me know.

can you pls add the workbook so i can follow along? Thanks!

Hi, why is the only connection option greyed out? Is there any way to fix it?

@DougHExcel

4 жыл бұрын

Sorry, don’t understand the comment/ question...

Is there is a row limit to this consolidation? I have approx. 150K rows of data from two different sheets. It appears the consolidation is incomplete due to row capacity. Please let me know. Thanks.

@DougHExcel

6 жыл бұрын

150K input rows is not a limit with Power Query. Check to see if there are any empty rows in the source data that separate the data.

@yasualmasih9058

6 жыл бұрын

You are right it wasn't a row limit. Actually it worked now, i forgot to refresh after edit. Silly mistake. Thanks for your help. :)

@DougHExcel

6 жыл бұрын

You're Welcome!

lengthy process

@DougHExcel

3 жыл бұрын

Malik Hassan Qayyum, that is very interesting...thanks for letting me know.

How realistic is it to color the cells based on the data source combinations the data matched with? For example, if all sources match, then color the cell pink. If only one data source had the item, color it green. Etc. Etc.

@DougHExcel

4 жыл бұрын

Sounds like something that conditional formatting would address kzread.info/dash/bejne/mZOk2pSdd7abhag.html kzread.info/dash/bejne/n5mjydF6j7C-nco.html

is excel 2013 able to do it? i unable to find power query in my tab option. any1 to help?

@DougHExcel

4 жыл бұрын

You'd need to download/install the power query add on from Microsoft website

@janecho38

4 жыл бұрын

@@DougHExcel thanks..i get it ady.

Hi, you can use the combine excel.workbooks :)

@DougHExcel

4 жыл бұрын

Hi cambike, thanks for adding to the thread!

I thought PowerQuery was designed to minimize repetitive steps. Would the function Excel.Workbook not allow you to gather all the named ranges I.e.sheets tables etc.. then select the sheets that you want

@DougHExcel

6 жыл бұрын

Yep, you're right...as with other Excel capabilities there's more than one way to skin a cat...this is just one of them :-D

@dcfrank9326

5 жыл бұрын

Hi, can you elaborate more on this excel.workbook? I've googled it but am not finding much.

Why is excel power query adding a unwanted column to the appended table when I refresh the data ?

@DougHExcel

4 жыл бұрын

There’s probably a difference in column headers. An errant space character?

@wayneseymour1

4 жыл бұрын

all exisingcolumns are formatted the same way after being converted to tables.

HELP HELP HELP--------hello, this power query combines data and you can update it at any time...the consolidate function does the same and also it can use sum as well...BUT it does NOT update if i add more data to the source. Can anyone tell me a way to use the power of consolidate BUT having an update to it...so if i add more rows to the source it will update the consolidation that u did first.

@DougHExcel

7 жыл бұрын

Sorry consolidate function is not as good with "automating" this process. Use of PQ is probably the preferred route if you see this becoming a recurring process.

hello, I have one sheet with only code(400,000) and another one index of the code with details (13,000).I would like to combine the information from the index sheet to code sheet. someone can help me?

@samrusaati9173

4 жыл бұрын

the code sheet is only one column but the index sheet is 10 columns

@DougHExcel

4 жыл бұрын

sounds like some lookups needed see these kzread.infosearch?query=power+query+lookup

What if i have 1000 tables.Then how to do it I can't go back and forth and dow everytime!! Pls help

@DougHExcel

3 жыл бұрын

Sorry, don’t understand the comment/ question... but maybe one of these could help kzread.infosearch?query=merge%20power%20query

What happens if the tables aren’t the same format?

@DougHExcel

4 жыл бұрын

You may need to go into the table in the power query editor to update the data types before consolidating.

@gabrielcastillo4126

4 жыл бұрын

Will give it a try. Thanks

auto-magically delicious

@DougHExcel

3 жыл бұрын

Hi Don’t Get Caught Slipp’n, thanks for the comment! 😋😋😋

Automagically

@DougHExcel

3 жыл бұрын

Yep, excel is magical 😁

It looks like a nice toy. But I have a dumb question: why not just make one worksheet from the outset and use filters to see north, south, etc...?

@DougHExcel

6 жыл бұрын

Hi David Skerritt, sometimes these sheets are given to you and if it is a recurring process, then PQ might help...thanks for the comment!

@mikecripps2011

5 жыл бұрын

Hey in my mind Excel is dumb as all this can be done with SQL and a database but some organizations spaz out when they hear database and sometimes data sets are only comparable with a few fields.

Not showing us how to get power query is not helpful

@DougHExcel

3 жыл бұрын

Jasmine S, that is very interesting...thanks for letting me know.

My problem is multiple sheets all containing same AND different info I want to combine in one overview. Problem though is duplicates... 😔

@DougHExcel

4 жыл бұрын

after combing you can use the remove duplicates functions in PQ

Thank you! Nice! My question is a little different so can you please help if you know the answer. Let us say that I have 2 Workbooks. One has 5 worksheets(It has all tables so 5 tables) How do I move all 5 tables/5worksheets into the second workbook all together rather than moving each tab separately (I can move worksheets but not tables) and the second question is when you move even single tab with (Move or Copy) option, you can move or copy either before or after particular worksheet. I have already named all worksheets in my second workbook and I want to move into those particular worksheets not before or after. Is it possible? Thank you in advance

@DougHExcel

3 жыл бұрын

If the goal is to take worksheets from a different workbook and put them in as separate worksheet in another workbook, it's still the same process of of either sourcing them as for a merge or append, but just not doing the merge/append part. Each worksheet that is extracted is just a separate query and then loaded again as a table in the new workbook. See if the other videos give some insight kzread.info/dash/bejne/q4Z7kq-ilpu7j5M.html kzread.info/dash/bejne/o6eardOzdq6YqrQ.html kzread.info/dash/bejne/ppmjrpako7jeaJM.html

@shoppersdream

3 жыл бұрын

@@DougHExcel Thank you! Separate Queries for 10 to 20 worksheets is going to be very time-consuming. I don't think that a power query will work for this issue. I tried it right now. It is fine if you are moving workbooks from a folder but not if you are trying to move worksheets (not exactly worksheets but tables). I can move all worksheets all at one time by pressing CTRL. This does not work for the table. Thank you for replying to my comment.