Hi,
I have a folder containing CSV files (C:\Users\User\Desktop\\1_ProvMonthly). The CSVs have variable length headers but each file shares, in different positions, the following 5 attributes (columns):
PCode
Type
Median Waits
Report Year
Report Month
I would like to be able to use Powerquery to loop all the CSVs files in the folder and extract the shared columns into one large table with an additional column at the start, indicating the file name from which the columns where extracted.
I have seen ways of retaining the filename e.g. https://community.powerbi.com/t5/De...sing-Folder-as-a-data-source-Power/td-p/22851
I am familiar with merging CSVs/Excel workbooks where the file schema is the same. What i have not seen is a way of merging CSVs based on column name matching where the columns may be in a different order in files i.e. need to be matched by name AND the total number of columns can vary between files from 18 to 23.
An internet search has not yielded any immediate answers.
Colin Banfield gave a helpful starter here:
https://social.technet.microsoft.co...narycombine-to-combine-files?forum=powerquery
But this doesn't handle variable length file headers. Setting the Columns parameter to the maximum number of 23 resulted in some fields being merged between files.
Does anyone know of a way to do this successfully?
I am using Microsoft Excel 2016 Windows 8 64 bit.
Thanks
I have a folder containing CSV files (C:\Users\User\Desktop\\1_ProvMonthly). The CSVs have variable length headers but each file shares, in different positions, the following 5 attributes (columns):
PCode
Type
Median Waits
Report Year
Report Month
I would like to be able to use Powerquery to loop all the CSVs files in the folder and extract the shared columns into one large table with an additional column at the start, indicating the file name from which the columns where extracted.
I have seen ways of retaining the filename e.g. https://community.powerbi.com/t5/De...sing-Folder-as-a-data-source-Power/td-p/22851
I am familiar with merging CSVs/Excel workbooks where the file schema is the same. What i have not seen is a way of merging CSVs based on column name matching where the columns may be in a different order in files i.e. need to be matched by name AND the total number of columns can vary between files from 18 to 23.
An internet search has not yielded any immediate answers.
Colin Banfield gave a helpful starter here:
https://social.technet.microsoft.co...narycombine-to-combine-files?forum=powerquery
But this doesn't handle variable length file headers. Setting the Columns parameter to the maximum number of 23 resulted in some fields being merged between files.
Does anyone know of a way to do this successfully?
I am using Microsoft Excel 2016 Windows 8 64 bit.
Thanks
