Log In
Register
Home
Active Member
August 18, 2022
Offline
I'm having trouble when merging two queries and expanding the result.
In the attached (see the 'All Assets & Revisions' query), in the third step when I expand the merged data, the values in the Revision Date and Monthly Finance columns in records two and three get switched. See the screenshots below.
I cannot work out why this would happen. Can anyone help? The file is attached.
Moderators
January 31, 2022
Offline
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
Active Member
August 18, 2022
Offline
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.
Moderators
January 31, 2022
Offline
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
Active Member
August 18, 2022
Offline
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.
Moderators
January 31, 2022
Offline
Glad it worked!
October 5, 2010
Offline
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
1 Guest(s)
