Hello friends, Hope all is Well & Safe! Please bear with me & please help me because I am losing my mind.
I am (via PowerQuery) combining Xlsm files, but the count of rows of some files that appears on PowerPivot are double the number of rows of the original file, why the # of rows of some are matching the row count, and others not.
Let me please give you the steps that I am taking.
- IT Provides me with CSV files, e.g. LA Jan-20 (8,000 rows), LA Feb-20 (3,000 rows), LA Mar-20 (5,000 rows), LA Apr-20 (2,000 rows).
- I save each workbook as Xlsm, and place all the workbooks in one folder (Proc DB).
- Apply the steps of Power Query: Import Multiple Excel Files From Folder Into One Data Model
- Run a PowerPivot based on the data model, and count the rows based on the combing of PowerQuery.
- PowerPivot gives these counts: LA Jan-20 (8,000 rows), LA Feb-20 (6,000 rows), LA Mar-20 (5,000 rows), LA Apr-20 (2,000 rows
- You can see that Feb-20 is showing double the row count; should've been 3,000, but instead got 6,000.
- Why the counting was identical with some of the original workbooks, and not with the others? Please remember every file and its contents are unique, i.e. doesn't have duplications (City Month-Year).
Your kind Help is truly appreciated & needed to fix this headache.
All the Best,
Hi Nawaf,
First, I'm not sure why you would re-save each workbook as a .xlsm file when Power Query can get the data from the original CSV files IT provide you with.
The only way Power Query would duplicate data is if it's being referenced again. Is the file containing the query also saved in the same folder as the .xlsm files you're getting? If so, that'll be why it's duplicating the data. Move the Excel file containing the query to another folder.
BTW, I also have a tutorial on how to get files from a folder with Power Query 😉
Mynda
Thank you Mynda! Always Awesome!
First, I'm not sure why you would re-save each workbook as a .xlsm file when Power Query can get the data from the original CSV files IT provide you with.
Reply: Because CSVs consume too much space.
Is the file containing the query also saved in the same folder as the .xlsm files you're getting?
Reply: No, ma'am it is not.
BTW, I also have a tutorial on how to get files from a folder with Power Query
Reply: Will follow your instructions instead and see the outcome. Thank you very much!
Are you sure the .xlsm versions of the .csv files are smaller? Usually it's the other way around i.e. Excel files are bigger than csv files.
