Table.Combine limiting data to 1000 rows per table in Service only

[hannahm]

Hiya,

This is my first post on here, I'm hoping someone can help me.

We're getting data from SAP BW, but we chunk it up into smaller queries as to not kill SAP BW - and then combine them in the PBI file. We have 4 SAP BW queries (one per year 2019-2022) with 250k to 1m rows per query, and 32 columns each. They have the same structure, same column headings, same format. Then we are unioning those in another table using Power Query:

= Table.Combine({#"Intake ABV Master - PBI CY", #"Intake ABV Master - PBI PY", #"Intake ABV Master - PBI P2Y", #"Intake ABV Master - PBI P3Y"})

When this is refreshed in the desktop, this table has > 3m rows. No errors that I can see. When we publish it, it looks fine in the service. But then when it refreshes using the gateway in the service, the table now only has 4000 rows - and when I split it by year it's exactly 1000 rows per year. The individual tables still have the full row count, so it's got to be something to do with the Table.Combine.

This was working fine until a user raised the issue about a week ago.

Has anyone else seen this before? Am I losing the plot? Does this function have a limit on it? Is there an alternative I can try?

Thanks

Hannah

[hannahm · Accepted solution]

I've found a solution to this.

Previously when you get data from SAP BW you have a "Cube Tools" menu on the header bar. Then when you renamed a column - this disappeared, almost removing the cube functionality from the query. I had added a Rename step to each of the 4 queries before I combined them. 

When looking at file now, I noticed that even after the Rename step, the queries still had the "Cube Tools" on the menu bar - almost like this now supported a column name change. I added in a column conversion to each step, which removed that Cube Tools menu item - and then the combine worked again.

There was a SAP BW patch applied this week and also a PBI update, so I'm not sure what the source of the change was.