Power Query Refresh issue

[Yuko_Tarby255]

Hello!

I just start learning Power Query and I could not figure out what I did wrong...

I created a test file with two worksheets. Followed a step by step instructions and I created a query table. I made a change on the source data, save then refresh, I got below result. If someone can help me, I truly appreciate!  

[#2]

@Yuko_Tarby255 That's difficult to answer without looking at the date and the query itself. Since it's just a test file, you can perhaps share it via Onedrive, Dropbox are similar. Always much easier to work with a file on hand.

[#3]

Would you please take a look?
https://1drv.ms/x/s!AmXz2i0hTvTlgew0ZFxFUkEuTpGZ_w?e=NkE4Ps

[#4]

@Yuko_Tarby255 Thanks. Can't tell what instructions you followed but must have missed a small step as your query connect to all "items" in the workbook. That is, the sheets for Jan and Feb, a hidden defined named, the output of the query itself and the sheet in which the output is stored. It creates a table for each of these "items", and you expand all of them upon each refresh.

So, I guess the first time you ran the query the workbook had only two sheets (Jan and Feb) and PQ combined all it found and loaded the result back to the table called TOTAL_COUNT in the sheet TOTAL COUNT and in the background it created a hidden defined name. You then changed some data in the Jan or Feb sheets, and refreshed the query. At that point PQ combined again all it found in the workbook. So not only Jan and Feb. The Expand step combines all tables you see in the picture below. Your TOTAL_COUNT table will grow bigger with every time you refresh the query as it will include a duplicate of itself plus all other items.

Before you delete all columns except the "Data" column and then expand, you need to tell PQ which items you want to work with. I guess in your case that's Jan and Feb. Then you should, for instance, filter out all Items that contain the word TOTAL to end up with this:

Now, the rest of the query will only work with these two tables and you will not duplicate the output from a previous refresh. And later on you probably add Mar, Apr, May etc. PQ will then includes these as well but NOT the items that contain the word TOTAL.

[#5]

Hi Riny,
Thank you so much look into this!! I am still struggling so, I created data file and PQ file separately then it worked. However, my goal is use one spreadsheet to do all. I do understand what you explained but would you please tell me how to lock the item?(Key icon). Thank you again.

[#6 · Accepted solution]

@Yuko_Tarby255 Sure, you can have all in one file, but In the step before you "remove other columns" you need to filter the Item column. It works similar to how you filter in Excel. Press the downward pointing triangle in the column header and define the filter you need. In this case I used "does not contain TOTAL"

By the way, I've been experimenting with PQ in Excel for the Mac when I looked at your file. In the PC version the pictures are slightly different but the principle is the same. So it will not look exactly the same on your computer.

You might find the link below useful in getting up to speed with PQ basics.

https://exceloffthegrid.com/power-query-introduction/ 

[#7]

Hi Riny,
OMG IT WORKED!!! I could change the source data or adding another worksheet and ALL WORKED! Thank you for being patient with me. I bookmarked the link and will learn more! THANK YOU!!!