Jun 13 2022 08:26 PM
Jun 13 2022 08:26 PM
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!
Jun 14 2022 09:10 PM
@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.
Jun 15 2022 05:52 PM
Jun 15 2022 09:24 PMSolution
@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.
Jun 16 2022 02:31 PM