Forum Discussion
Merge multiple excel files with slightly different date fields
Hi All
Pls can anyone help with this issue. I would like to merge tables from different excel files, with slightly different date columns
For every month, first date column will be current month and last date column will be the 5th month from current.
For example, i would like to combine May, Jun & Jul file -- to the format of Merged File as below. All the months from the each file will be shown on the Merged file and replace with a "0" if a particular month doesnt have data for the date column.
I have about 24 files to combine ( 2 years worth of data) to a single consolidated Merged File format. Every month there will be a new file released for the month and i hope the consolidated Merged File can include the new file once i refreshes it.
Really greatful if anyone can provide some form of guidance. Will make my work way easier. Thanks very much in advance!
Best Regards
Munch00
5 Replies
If you try the following steps it should work:
1) Use Get Data from Folder
2) Select folder containing all the files.
3) Click Transform rather than merge.
4) Filter to just the files you want.
5) Click the doube arrow button at the top of the Content column:
6) It should write a basic merge function:
The key is to check that the "Transform Sample File" (or if you're ok with the advanced editor the fx funciton itself) has no direct column references. With my example it produced:
let Source = (Parameter1) => let Source = Excel.Workbook(Parameter1, null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data] in Table1_Table in Source7) If all has gone to plan you should have all the columns you need. (If not there are other techniques for dealing with files that vary more than you originally thought!.
😎 If you select all columns, right click and replace values null with 0 it will work for the current selection.However won't work when new columns are added. You can however modify the M code from:
= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Source.Name", "Month of File", "Item", "22-Jul", "22-Aug", "22-Sep", "22-Oct", "22-Nov"})to
= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type"))(#"Changed Type" references the prior step).
The key thing to using Power Query for anything like this is to ensure you never explicitly reference column names.
Personally I would actually have gone into the "Transform Sample File" right at step 6, selected the "Month of File" and "Item" column. and then clicked "Unpivot other columns". This gives a form that's much safer to work with and also generally easier to use within Power Bi. (Long and Thin fact table!)
Hello Ben Dobbs
Appreciate your explanation 🙂 I'm trying to use the 2nd method u suggested - which is to unpivot all other columns.
This is what i had in mind now.
1. For every raw file, create a new excel file with the unpivoted table format.
2. Place the new excel files under New Folder
3. Create another excel file to combine the files in New FolderHowever...this requires me to manually create the new excel files so Power query can combine the files.
Is it possible to for Power Query to transform all the files in the folder to the desired table format (without me having to manually do it for every file... since it is the same transformation) + combine them at the end into a single table?
Many thanks again.
See the note at the end of my original post:
"Personally I would actually have gone into the "Transform Sample File" right at step 6, selected the "Month of File" and "Item" column. and then clicked "Unpivot other columns". This gives a form that's much safer to work with and also generally easier to use within Power Bi. (Long and Thin fact table!)".That will have unpivoted each file in turn.
Convert them to a tabular list using the unpivot command .....
For each file ...
Remove the month if year column
Click on the Item column and Unpivot other columns.
Repeat for all tables and then append the tables.
So you left with a smple tabular table with
Item, Date
Please click thumbs up and accept solution
