Forum Discussion
Refresh But Compare
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.
2 Replies
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.
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.
