Error message when expanding second table in merge: "The 'offset' argument is out of range"

[AC246]

Hello all, I'm coming across a mysterious (to me) error message when I'm trying to run a left outer join to merge two queries together. The merge seemingly works well, but then when I seek to expand out the second table in the join to show its individual columns, I'm getting this message:

Expression.Error: The 'offset' argument is out of range.
Details:
4

I've tried varying a few things for that expansion step, for example, selecting fewer columns from the second table to include; but the error still comes up. I haven't encountered this for previous, similar merges before. Googling has not yielded much advice. Might anyone know what this error means in this context, and how to work around it? Thank you for your help.

[Shubham_rai955]

@AC246 , if my solution helped you in any way, please mark it as solution

[AC246]

To be honest, I figured it out just before seeing your message.

[Shubham_rai955]

This error usually happens because Power Query is getting confused with the data in one or both of your tables during the merge and expand step. Common causes include mismatched data types between join columns or problems related to query folding and data refreshing.​

Solution can be:

• Make sure that the columns used to merge the tables have the same data type (e.g. both text or both number).​

• Before the merge, add a step with Table.Buffer() to your source table in Advanced Editor. This can stop Power Query from reorganizing (folding) the steps and helps avoid "offset out of range" errors.​

• Remove any empty, error, or null rows in the columns you use for joining, if possible.​

Try these steps and your table expansion should work without this error. If you still see the error, check for hidden or special characters in your data sources.

[AC246 · Accepted solution]

Thank you @Shubham_rai955 and @PhilipTreacy for your suggestions. I've finally figured out the problem, and I think it is effectively an example that falls within the suggestions you've both made: the matching identifier being used, ISSN number, is often used in the format '1234-5678', which was the case in both of the tables here. Removing the hyphens from the ISSNs in both tables --> a nice obedient merge using the '12345678' identifiers. Phew! Thanks again.

[PhilipTreacy]

@AC246 

Be good if you can provide some idea of the data you have, what is in the tables etc.

The tables that you are trying to expand - do they all have the same structure/number of columns?

When you get an offset error like this it means PQ is trying to refer to something that doesn't exist.  Likely it thinks there a table column at row offset but it is't there.

The table in the first row might have 4 columns, but some subsequent row has a table with less than 3 columns.

Phil

[AC246]

Hi Phil,

I'm not sure what details would be most helpful, but the merge is a left join between two tables; the first table is a list of academic articles and associated details (year of article publication etc), put together from a couple of sources via previous merges. The second table is a list of academic journals and associated details. The theory is to join the tables, matching on journal identifier (ISSN), to bring the additional journal information into the list of articles as additional columns, so that each article has additional information about its respective journal added in. Both tables look fairly standard to me in terms of structure.