Power Platform Community Forum Thread Details

Power Query - Populating Excel with Pivoted Tables - Strange Behavior

Hi all,

New to Power Query and I'm seeing some strange behavior (or maybe it's a known issue?) when using PQ to query data from a database and drop it into Excel. The trouble seems to be with 1) pivoted data and 2) the use of formulas that reference the landed data.

Assume a db table with Order Date Month, Order Type and Quantity. I have a query that aggregates Quantity by Order Date and Order Type and filters for the current month.

If I use Power Query to load this data, applying no additional transformations, into excel, I can create a formula using SUM against the Quantity column and it works. If I change the month I'm filtering by and refresh to get a different month's data, the formula survives and shows me the sum of the new values.

I don't have this success if I use PowerQuery to Pivot on Order Date Month. The initial query will load fine, and subsequent queries load fine as I change the Month Filter.

However, I create a formula outside and below the table range to perform a SUM, and initially it works, but if I refresh the query, an interesting thing happens - the formula (which was below the quantity cells) is removed...seemingly overwritten by an empty cell.

Another observation as the results are sent back to Excel...you can see...it's extremely brief...Excel seemingly bringing in 3 columns, then converting to a pivot with 2 columns - it's like it needs to insert 3 columns in (the form in Power Query before the pivot), then only takes 2 of them (the form in Power Query after the pivot step). This "pushing the formula to the right" behavior actually continues each time I change the month filter. If I leave it the same and refresh, no "pushing to the right".

Any thoughts? The goal is to create a number of sheets (with pivoted tables via Power Query) with formulas that reference these cells/ranges where I an dropping off the data from Power Query and have that refreshed with new data every month.

Quick Links

Leaderboard > Power Apps - Power Query