Automated Trending in Power Query

[SadieCake]

Today, we manually manipulate tables w/in PowerQuery to present trending week-to-week, month-to-month, and quarter-to-quarter so that are partners can see trending data over time using CSV files. Moving to a datalake that will now import the information into Power BI automatically, is there a way that it can perform trending from an automated fashion or do I still need to go in and manipulate the information each week/month/quarter?

[Anonymous · Accepted solution]

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.

[SadieCake]

My apologies for the late response. Yes Rohit, your response was right in line. Thank you for being so explicit in your response. I will test this out. I appreciate you!

[v-kpoloju-msft]

Hi @SadieCake,

May I know if you have tested it out? If yes, and the provided reply has worked for you, please accept @rohit1991, reply as the solution, as this will be helpful to other community members who may face a similar problem in the future.

Thank you.

[v-kpoloju-msft]

Hi @SadieCake,

I wanted to check if you had the opportunity to review the information provided by @rohit1991. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-kpoloju-msft]

Hi @SadieCake,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-kpoloju-msft]

Hi @SadieCake,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[rohit1991 · Accepted solution]

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.

[Anonymous · Accepted solution]

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.