August 23, 2019
Hi,
I've loaded three files - of about 20 MB each - from a network folder into PowerQuery and formatted the columns and made a number of changes. During this process, I loaded additional data in one column and I can see that PowerQuery searches through all three files in the folder for additional unique entries and adds additional data to the list from that column. I then do a merge from the left, with another query to add two columns from the second query. All looks fine.
When I load this merged query into Excel and connect it to three different dimension queries in Power Pivot, it works fine too. But, when I start producing pivot tables and charts from the Power Pivot window, I can only see data from the first file with entries for the year 2020. The second and third files with data from 2019 and 2018 are not present.
Any suggestions? My current workaround would be to put all data into the first file, but it would be neat to keep the file structure in the folder intact if possible.
Regards,
Jan, Sweden
July 16, 2010
Hi Jan,
While it might appear to be working fine in Power Query, it's obviously filtering out the 2018 and 2019 data at some point. I'd step back through the queries and filter the year column to see if there is actually data for those years. I suspect it might be being dumped at the merge step.
Mynda
August 23, 2019
Hi Mynda,
Thanks for your reply and guidance.
I found the issue right at the beginning of the Query changes made to the files. The file table names were not identical in the three files, which I wrongly assumed. Power Query switched to another sheet in the second and third-year files that were named "table1". I switched back and voila, I got the missing two years back in my Excel file. All the best.
1 Guest(s)