Forum Discussion

SadieCake's avatar
SadieCake
Frequent Visitor
1 year ago
Solved

Automated Trending in Power Query

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?

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

  • rohit1991's avatar
    rohit1991
    1 year ago

    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.

7 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    1 year ago

    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's avatar
      v-kpoloju-msft
      Community Support
      1 year ago

      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's avatar
        v-kpoloju-msft
        Community Support
        1 year ago

        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.

  • SadieCake's avatar
    SadieCake
    Frequent Visitor
    1 year ago

    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's avatar
      v-kpoloju-msft
      Community Support
      1 year ago

      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.