Forum Discussion

Centaur's avatar
Centaur
Helper V
4 years ago
Solved

Apply a Rate

Hello,

 

I am a novice user of Power Query. 

I have a problem I cant figure out. 

Grateful for the assistance. 

here is what I need to do : 

 

  1. I need to apply a rate from a rate table and apply that rate to a pivoted Power Query data set
  2. The rate is dependent upon 2 criteria:  ProjID and the Date. 
  3. I need to apply the rate from the rate table if the record's Date as found in Row 1 (its a header row, pic below) is >[Date From] and <[Date To] in the rate table.  But it must be on [ProjID] match
  4. If the records date is in that range and a match on [ProjID] as found on the rate table then apply that rate from the rate table.   

 

I have 2 Connections in Power Query (the pivoted data set based on the raw excel data and the rate table)

I dont know how I can add the rate to the pivoted data set. 

Maybe its an IF statement?

Below is a link to the file since I cant upload on this website. 

https://1drv.ms/x/s!AkvCjNvcBhpQiPRV5B_j-qzVOgpMhQ?e=PmHewK

the rate table:

 

this is the pivoted table and I want to append the rate above to this table (according to the criteria):

 

 

Any assistance is greatly appreciated.  Thank you

Need Help
Tips and Tricks

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Centaur 

     

    You can create a measure to get the rate according to project ID and dates periods. Put this measure into Values of your pivot table. I create the following sample with Power BI. It's similar in Excel's Power Pivot table. 

    m_Rate = 
var __date = MAX('data'[dte])
var __projID = MAX('data'[ProjID])
return
CALCULATE(MAX(Rates[Rate]),'Rates'[ProjectID]=__projID,'Rates'[Date From]<=__date,Rates[Date To]>__date)

    Reference: 

    Measures in Power Pivot (microsoft.com)

    Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships, Data Modeling & Much More! - YouTube

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Centaur's avatar
      Centaur
      Helper V
      4 years ago

      Hi, thank you for the response.  I will try to implement but I want to ask a question first.  After looking at the header row, the dates are not in order in my sample file posted. you can see they are out of order if you scroll all the way to the right.  I am not sure why PQ is sorting in this fashion.   I need all of the dates in order from left to right.  Do you see what i am referring to?   do you happen to know why it does this?  I have resorted to using excel but I would like to have a PQ solution as well.   

       

      here is a screen shot

      must scroll all way to the right.