Refresh But Compare

[Medmanage101]

I have a simple Excel sheet that gets updated and changed weekly.  Hitting 'Refresh' works great but now last week's data is gone.  Any ideas on how I have a visual that shows the comparisons of each week?  There is not a date column in my data source.   It is simple data...a list of employees.  This changes every week and I need to see those changes.  (30 down to 27 this week.)  But my 30 does not exist once the list is updated.  It only shows a current list of employees at 27.  Could Power Bi add a date column every time I hit refresh?  Ideas? Help.

[Medmanage101]

When I 'Append' I just got double the numbers (employees).  Not two sets with different dates and times.  I went from 20 employees to 40, all with the same date and time every time I refresh.

[Awm]

Certainly! Here's a detailed step-by-step explanation:

1. Duplicate Data:
- In Power BI, go to the Power Query Editor by selecting "Transform Data."
- In the Query Editor, find your data query, right-click on it, and choose "Duplicate." This creates a copy of your existing data query.

2.Add Date Column:
- In the duplicated query, click on "Add Column" in the ribbon, then select "Date & Time" and choose "Date." This adds a new column with the current date for each row.
- If you want an automated date, you can use the formula `DateTime.LocalNow()` in the new column.

3. Combine Data:
- Still in the Query Editor, select the original and duplicated queries.
- Go to the "Home" tab, click on "Combine Queries," and choose "Append Queries." This combines both queries into one.

4. Load Data:
- After combining the queries, click on "Close & Apply" to load the data back into Power BI.

5. Visualize Changes:
- In your report view, create visualizations using the combined data.
- Include the newly added date column in your visuals to track changes over time.

6. Refresh:
- Each time you hit the refresh button in Power BI, the duplicated query captures the current data with a new date, preserving historical snapshots.

By following these steps, you'll have a setup that maintains a historical record of your data, allowing you to analyze and visualize changes over different weeks.