Forum Discussion
Need help with a merge query/expand table problem
I have a problem a merge query/expand table not displaying the correct data form the merged query after the table has been expanded.
The merge is built around 4 columns in each table, and I have tried it both by connecting by the 4 separate columns, then merging this columns into a single column (the state it is currently in) in case there was a problem with the number of columns linked.
Here's an example row from the query I am trying to merge into my main query:
Here's the merge query set up:
Here's an example of what the data looks like after the merge:
But here's what it looks like after the table is expanded to add the PERIOD_INDEX field to the table:
It seems that something is going wrong with the expand, because the PERIOD_INDEX value of 2 for this example is somehow becoming a 1 when the table is expanded.
What am I doing wrong here?
The solutions suggested here haven't been helpful, unfortunately, but I have found the solution elsewhere - I needed a to put Table.Buffer around a Table.Sort function. Aparently the add index column was trying to recalculate during the merge tables which is what was throwing it off.
6 Replies
- MFelix
Super User
Hi BenRedmond ,
Is there more than 1 row for each of the values in both tables or the table you are merging only as a single value per row?
Table SUMMATIVE_ASSESSMENT_MARKS has multiple rows, but table ASSESSMENT_PERIOD_INDEXES only has one for each value. Looking at it a bit further, it only seems to be showing 0 or 1, despite there being values from 0 to 3 in the merged table.
- sannavajjala
Resolver II
Hi,
It looks like the merge itself is working correctly, since the nested table shows the expected PERIOD_INDEX = 2 before expansion. That usually means the issue is happening during the expand step rather than in the join.
One thing I'd check is whether the lookup table contains multiple rows with the same IndexKey. If there are duplicate matches, Power Query can return multiple records in the merged table, and the expansion may not be pulling the row you expect. Try filtering the lookup table for Science|--|Year 9|This|Year 9|MidYr and verify that only a single matching record exists.
I'd also review the Applied Steps after the expansion. Sometimes a later step such as a merge, remove duplicates, group by, sort, or changed type can alter the values, making it appear that the expand operation caused the issue when the value was actually changed afterward.
Another useful test is to expand not only PERIOD_INDEX, but also another identifying column such as UniqueID or Period. If those values don't match what you see in the nested table before expansion, then the merge is likely returning multiple matches or the key isn't as unique as expected.
Based on the screenshots, my suspicion is that the merge key is not unique in the lookup table and Power Query is expanding a different matching record than the one you're inspecting. I'd start by checking for duplicate IndexKey values and confirming the merged table contains exactly one row per key before expanding.
Thanks & Regards,
Manoj Annavajjala
v-kathullacCommunity Support
Hi BenRedmond ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
The solutions suggested here haven't been helpful, unfortunately, but I have found the solution elsewhere - I needed a to put Table.Buffer around a Table.Sort function. Aparently the add index column was trying to recalculate during the merge tables which is what was throwing it off.
- v-kathullac
Hi BenRedmond ,
Glad to hear that you resolved your issue Let us know if you need any further assistance.
Thanks,
Chaithanya
