How to use Powerquery to merge named columns from CSVs in folder?

HarrisQ

New member
Joined
Aug 24, 2017
Messages
1
Reaction score
0
Points
0
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
 
Back
Top