Power Query - fill in gap on data load

[espenskeie]

Hi

I have an issue where I have interest rate from historical data and up until somewhere around this period.

I also have an payment plan going forward, and I need to use the latest interest rate to fill the gaps. I have done this in programs like QlikView where you can define the load direction, and then use a function to pull data from previous row and Write this into current row.

Does any one know how I can solve this in Power Query M?

The last column somehow shows the missing values, except from that this value is not from the last row of Security no 3, it is from the last Security number in the Whole list.

I tried to solve this in a formula, but I would prefer to do the job in the load of data, as I think that is more robust.

Here is my formula, I have searched for the SecurityPayment With highest sequence number, but it does not Group the values by Security number, that is why my interest rate is Equal to the value from the last row in the data table:

If I could do this in the load instead it would be preferable.

Column = IF(tblSecurityPayments[FromDate]=Blank();CALCULATE(SUMX(tblSecurityPayments;tblSecurityPayments[InterestRate.1]);FILTER(tblSecurityPayments;tblSecurityPayments[SecurityPaymentSeq] = MAX(tblSecurityPayments[SecurityPaymentSeq]));tblSecurityPayments[InterestRate.1]))



Best regards
Espen

 

[Matt Allington]

This article may or may not help you. https://exceleratorbi.com.au/fill-table-with-last-survey-result/. My article shows how you can display the last value. If you want to generate new data points, then you will need a different approach.