Forum Discussion
Which formula to use to get data from 11 sheets to trial balance
Thank you, I tried to do it via Power Query, from eleven different excel files, to get one table. Generally it work fine. Mine issue was, when I changed one number in one of excel files, I refreshed power query and it was updated. But than out of this query table, I made pivot table myself. When I refreshed pivot table, it didn't update. Is it normal or I have to do it another way? In file you can see in green tab E2 it updated to 10.000, in sheet 2 where should be the pivot table, it did not update.
If it works after you Refresh All, wait a bit, Refresh All again and PivotTable shows correct data - disable "Enable background refresh" for Power Query connection. More details is here Enable Background Refresh in Power Query - Solve and Excel Consulting
It works with these selections in the Pivot Table. I've applied a filter for only a few accounts (KONTO in german Excel) in column A. However this a very unusual way of using Pivot Table. In my opinion the data from Power Query must be unpivoted.
I think you have to unpivot the data in Power Query in order to return a result like in my sample file. In the screenshot below is the result from Power Query in the green table. This result is ideal for a Pivot Table.
