Hi Mike,

Changed your staging query as I didn't really understand why you included a grouping step. The attached file seems to do what you want.

If not, come back here.

Riny

Hi,

Thanks for looking at this.  I need to add an index that starts at 1 for each revision per Asset ID.  That's why I used a grouping step rather than just adding the index to the main table.

I suspected that the grouping step is causing the issue but I have no idea why.

Hi Mike,

Understood! I added an assets to see what exactly is happening. The grouping isn't causing the problem. The sorting step in the staging query is the cause. Can't explain why it happens but PQ and haven't seen this happen with such small data sets. But somehow, PQ looses track of things upon sorting. Then you can force PQ to "fix" the table into memory by adding the Table.Buffer function. Did that and now it works.

Riny

Edit:

If you would rename the Date columns in the Asset List and the Lease Revisions to just "Date" (i.e. not "Original Date" and "Revision Date") you could use the following query to create the desired output in one go, without the staging query.

let
Source = Table.Combine({#"Asset List", #"Lease Revisions"}),
Sort = Table.Sort(Source,{{"Asset ID", Order.Ascending}, {"Date", Order.Ascending}}),
Merge = Table.NestedJoin(Sort, {"Asset ID"}, Rebates, {"Asset ID"}, "Rebates", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "Rebates", {"Rebate"}, {"Rebate"}),
GroupedRows = Table.Group(Expand, {"Rebate"}, {{"Grouped", each _, type table [Asset ID=nullable text, Date=nullable date, Monthly Finance=nullable number, Rebate=nullable number]}}),
AddRev = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([Grouped], "Index", 0, 1, Int64.Type)),
Combine = Table.Combine (AddRev[Custom])
in
Combine

Thanks.

My actual workbook is far more complicated than the attached (which I just produced to show the error) so your second solution won't necessarily work.

Buffering the table just after the sort step works though.

Thanks for your help.

Glad it worked!

Hi Mike,

data in PQ isn't stored in a consistent state - that is, the view you see of the data and the way PQ is actually storing it, isn't necessarily the same.  As you've seen with the sorting, you are expecting the data to look one way, but you get a different result than expected because PQ is actually storing the data differently than you think.

You can force PQ to store the data in a known/consistent way by using Buffer functions like Table.Buffer and List.Buffer.  These Buffer functions load the data into memory and the data is then stored in an ordered way that is not changed until the data is released.

I've written a couple of blog posts that illustrate this behaviour and may help to shed light on the way PQ works

Dense Ranking in Power Query • My Online Training Hub

Quickly Create Running Totals in Power Query • My Online Training Hub

Cheers

Phil