Merge multiple excel files with slightly different date fields

[Munch00]

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

[speedramps]

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

[bcdobbs]

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
    Source

7) 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!)

[Munch00]

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 Folder

However...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. 

[bcdobbs]

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.

[Munch00]

Hi Ben Dobbs

I tried it but still face with some issues. Could u advise how can i solve it pleasee?

1. I placed all the raw files in a folder and i clicked "Transform" instead of combine. Then, i clicked on the double arrow.

2. Under "Transform Sample File" tab..i will need to clean up the raw data a little (remove unnecessary rows and columns, promote first row as headers) , then i unpivot other columns to change to Tabular format.

Before

After

However, under the "Merged" Query..i run into some errors. So i am unable to sucessfully combine the files.

( i cant find a way to attach my raw data..so im sharing via in case u need it)

https://drive.google.com/drive/folders/1nqGmjpUri1S0iokht1DrIAaRwfrI9ukU?usp=sharing 

Thanks for ur help !