Forum Discussion
Adding Data Points to a Table or Chart After Power Query Refresh
I have an Excel workbook that has a worksheet for calculating who doesn't have access to our system based on content on the site. These calculations use XLOOKUP to compare two other workbooks (1 CSV and 1 XLSX). I then have Power Query clean up that sheet to generate a report. The CSV and XLSX are also cleaned up with Power Query prior to the calculations taking place.
The output of the final report looks like this:
I would like to know the total count of people, the unique count of people, the number of people who are the sole owners, the number of people who are listed with other people who do have access. As you can see behind the blur under "Triggered Owner", there can sometimes be more than one. The tricky part here is that I would like to track these numbers in a new row in a table for each refresh, similar to the below, where the data doesn't completely erase each time. Run Date is when the refresh occurs.
Is this possible?
4 Replies
Assume you have initial query which returns blue table and loads into the grid. Let name it Result.
Query returned table from the grid, name it Result Old. Be sure it's Connection only.
Duplicate Result query, rename it as Result New, connection only as well.
Now substitute M-Code for result on
let Source = #"Result Old" & #"Result New" in SourceThat's all. Now data in the table in grid will be kept as it is, on each refresh new data will be appended to that table.
Good morning. I was able to follow the above. I created the table as a query, replaced the dummy values in the table with Excel equations to calculate everything, and updated the column titles. I followed your instructions but am not sure how to add the refresh date and calculate everything onto a new row.
1.When you load the data from Power Query into Excel, use an Excel Table (Insert > Table) rather than just a range. Excel Tables automatically adjust their size when data is added or removed. This ensures any charts linked to the table will reflect the updated data after a refresh.
Confirm that your Power Queries for both the CSV and XLSX files are set up correctly. Make sure that they effectively transform the data into a format suitable for your calculations.
