How to iterate through the rows in a Power Query table applying formulas to each row's data

[Stilish]

I am trying to build a FX rate regression model using Power Query that tests a stop loss strategy looking back at historical exchange rates.

I've developed my model to the point where I need to start iterating through the rows(each row represents a time period) in the query to determine when the relevant stop-loss will trigger and at what rate. I've included a picture below that hopefully explains what I am trying to do.

I would be most grateful if someone could guide me how to do this. I have had a look at List.Accumulate but struggling to see how it would help. @Greg_Deckler and @edhans , you guys seem to really know your stuff..😁

Thanks

[edhans]

I'm not clear exactly on what that pic is showing.

Can you share some sample data?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Stilish]

Hi edhans

Thanks for your message.

Here is the link to a google drive folder with 2 files in it

Google Drive Link 

The first file is simply the file I copied the picture from.

The second file is my actual model file with Power Query connections included. Please excuse my novice attempt.

The "Detailed Steps Excel Basic" sheet sets out how I would achieve my objective using basic excel. I have since enhanced the file by improving the formulas and also by passing the data to an array and using VBA. But I need to use Power Query for larger data sets.

Only the first two rows calculations are included but obviously they would need to be repeated for each row in the data set.

Thanks again

[edhans]

Ok. I am not sure I 100% understand what this is doing. I trade ETFs about once every 5 years, so stop losses and such are way outside of my zone. 😁

I used your Excel sheet to generate the query in the code box below, and the last column is the math I think you want. The key is this formula:

if [open] < [#"Stop-Loss"] and [#"Max Stop-Loss"] <= [#"Stop-Loss"]
    then List.Max({[#"Max Stop-Loss"],[low]})
else
    if [low] < [#"Max Stop-Loss"] 
    then [#"Max Stop-Loss"] 
else 0

See if that works. If not, ping back with where I blew it in understanding what you needed here. 

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcvdCYAwDATgVaTP0ibNT6+uUrr/GhqsKBICH7ncGIlLLZUYGw62tCfWDHFfcmnPzbBk6KHYt0t0p27XBC0LumCVVLwxR0C5codCf/48zXkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [time = _t, open = _t, high = _t, low = _t, close = _t, Period = _t, RefRateTime2 = _t, #"Reference Rate.Reference Rate" = _t, #"Take Profit" = _t, #"Stop-Loss" = _t, #"Loss Interval" = _t, #"Adjustment Interval" = _t, #"Max Stop-Loss" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"time", type datetime}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"Period", Int64.Type}, {"RefRateTime2", type datetime}, {"Reference Rate.Reference Rate", type number}, {"Take Profit", type number}, {"Stop-Loss", type number}, {"Loss Interval", type number}, {"Adjustment Interval", type number}, {"Max Stop-Loss", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [open] < [#"Stop-Loss"] and [#"Max Stop-Loss"] <= [#"Stop-Loss"]
    then List.Max({[#"Max Stop-Loss"],[low]})
else
    if [low] < [#"Max Stop-Loss"] 
    then [#"Max Stop-Loss"] 
else 0, type number)
in
    #"Added Custom"

[Stilish]

Hi @edhans

Thanks for your response. A couple of things:

1. Yes your logic is correct for the first period assessment. But I can’t see where you got your “Max Stop-Loss” from?
2. I don’t understand where your source is and what it contains? I would think that it should come from the “Data with Ref Rate” query. (In case it’s relevant I have added the source file to the Google Drive Link. Sorry I should have included it previously.)
3. I did as you said but had to remove step 2; Changed Type to remove the error of not being able to convert to number. (Picture in Google Drive Link)
4. When I do this I only get the result for the first period assessment for the first row which correctly shows nil. But since the result is nil it needs to evaluate the next row (as set out in column Q of the “Detailed Steps Excel Basic” sheet), etc until it triggers which would be in period 10 at 14.8764(see cell Z29). This evaluation doesn’t seem to be in your code. But maybe you’re just checking that you get the first level evaluation correct?
5. Also the result only shows the first row result doesn’t show the result for the remaining rows. (Picture in Google Drive Link)

Thanks, I look forward to your response.

[edhans]

Using the same numbering as you did:

1. it is from your Max Stop Loss column in the Excel file. Now, as I look at that, it is a rather complex formula. But there is a problem. This is your formula:
   1. =MIN(($H$3+$L$3*ROUNDDOWN((C3-$H$3)/$L$3,)-$K$3),(B3+$L$3*ROUNDDOWN((C3-B3)/$L$3,)-$K$3))
   2. The problem is the $Column works fine, that is how Power Query works. The $Row however, does not. You cannot reference cell C3 in Power Query. You reference a specific column, this row. It is the same with DAX, but DAX does let you access other rows and then get what you need by applying filters, or using functions like SUMX, MAXX, etc. that will let you target specific amounts in a column.
   3. I am not saying this is not possible. I am 100% sure what you need to do is possible. But you will have to stop thinking like Excel and approach it from a different angle. For example, if you are always using $H$3 as the Reference Rate, then I would create a variable in Power Query. In a blank query, just type = 1234.556 and name it varReferenceRate. You can then access this value from all of your queries anywhere.
   4. The problem is I don't understand your 2nd row for this, which is:
      1. =MIN(($H$41+$L$41*ROUNDDOWN((C41-$H$41)/$L$41,)-$K$41),(B41+$L$41*ROUNDDOWN((C41-B41)/$L$41,)-$K$41))
   5. Now it says $H$41 (you skipped about 40 rows with your very helpful IF/THEN/ELSE explanation. But why isn't the formula $H41 and not $H$41? It seems if you copied this down, you'd have to manually change the reference, unless I am misunderstanding it.
   6. Bottom line here is I'd need to really understand what this formula is doing, then figure out how to do this in the world of Power BI, and decide is Power Query the right place for it, or is DAX, and what is depenedent on it, and where is that better, PQ or DAX, etc. This is where I break out whiteboards and start writing all over the place. The point I am trying to make here is you cannot often take an complex Excel model that is in a grid format and expect to drop it into Power Query or DAX and just translate the formula language from Excel to M or DAX.
2. used your excel file as the data source (I pasted it directly into the Power BI model for that record). I was really struggling to understand how your data worked in the PBIX file, but you gave a much clearer example of the data and the IF/THEN/ELSE logic in the Excel file, so I used that. I hope it is useful. If I've confused the issue at all by doing this I apologize, and feel free to ping back for clarifications.
3. Yeah, the Change Type step was specific to the Excel data above and may not work with your exact data table you are pulling in.
4. Ok, this is an issue. Power Query doesn't really know how to move between rows. There are tricks to do this of course, but performance will begin to lag on large data sets. Honestly, if this were my project and that was a requirement, I'd be inclined to add an INDEX column in Power Query after the above logic is done, then use DAX to do this, as it is built to move between rows and filter data.

I think your question is good one and would be an interesting project to migrate to Power BI. Again, 100% confident it can be done, but it is, I think, really outside the scope of a question in these forums.

Does that make sense? Not trying to brush this off, but my next step honestly is to stop posting in the forum with something like this and arranging a phone call, screen share, and getting a deeper understanding of what the Excel model is doing, and the exploding that logic and putting it back together in the world of Power BI.

I think if I were you and didn't want to call in a consultant, it would be to largely do what I've described above, then post back specific questions on specific issues - and there could be half a dozen or more - vs trying to get this in one response.

[Stilish]

Thanks @edhans 

OK, I understand re: where you got the Max Stop-Loss from. As you can see in excel it is a calculated field which I would want to replicate in PQ.

Based on your whole comment I don’t think it’s relevant to deal specifically with “a” through “e”, except to say that it jumps from 3 to 41 because I needed the intervening rows to set out my explanation in column Q; otherwise it would have gone from row 3 to row 4. 🙂

f. Yes that is the conclusion I am coming to myself. See 4 below.

2. I think it just confused things because of the intervening rows. That’s why I put the actual source file into the folder.

3. OK

4. This is precisely what I was thinking. It does seem like DAX would do a better job; the question is how to get the data into the Data Model. Do I bring it directly from the source or do I take it as far as I have/can in PQ and then pass the final query over to the data model and continue with the iteration formulas from there? What do you think and what is the best way to do this? Also, will the link between PQ and data model remain so that if I change one of the inputs to PQ it will flow through to the Data Model?

If you are willing to do a Teams call and then I can screen share and talk you through the logic if that will enable you to answer my questions in point 4. And from there I agree I will then attempt the solution myself rather than use a consultant as I’m enjoying the challenge.

Thanks again. I look forward to hearing from you.