Automated Trending in Power Query

There is no automation in Power Query. The best place to do that is a new table in the source data (data to Lakehouse/warehouse) and use in current data be a relationship to the proper grain. Maybe there is a way in DAX to create a measure for what you are trying to do.

Hi SadieCake 

Power Query does not support automation it is used for data transformation before loading into Power BI. To automate trending, use DAX and a proper data model, not Power Query.

How to Automate Trending Correctly

1. Store Data in a Central Source (Lakehouse/Warehouse)

• Ensure your source stores historical data rather than relying on Power Query.
• Use a Date Table at the proper granularity to enable trending.

2. Use DAX to Calculate Trends Dynamically

• Create measures that compare data across weeks, months, or quarters.
• Example DAX measures for automatic trending:

Week-over-Week Change Sales Last Week = CALCULATE([Total Sales], DATEADD('DateTable'[Date], -7, DAY))

Month-over-Month Change: Sales Last Month = CALCULATE([Total Sales], DATEADD('DateTable'[Date], -1, MONTH))

Quarter-over-Quarter Change: Sales Last Quarter = CALCULATE([Total Sales], DATEADD('DateTable'[Date], -1, QUARTER))

3. Use Incremental Refresh for Performance

• Instead of reloading all data, set Incremental Refresh to update only recent records.
• In Power BI Service, configure refresh policies to keep historical data while updating new entries.