Merge data from Different Excel Files

[mrbuttons07]

Hello guys!

I am trying to combine two sets of table with different headers/formats into one master format. Using the append query method didn't work since both tables consists different formats. (Refer photo below)

Is it possible to merge those two tables of different sources using merge queries? Also, is it possible to match two columns together as seen above?

For example: Match Data.MES Batch with Data.SAP Batch. 

Thank you everyone for your support! 😊

[Jimmy801 · Accepted solution]

Hello @mrbuttons07 

you need to provide the programm a "matching-List" that is then used to rename the columns of one table. After that you can combine both tables

here the complete code

let
    DataMES = 
    let
        Source = #table
        (
            {"Data.MES"},
            {
                {"DataMESA"},	{"DataMESB"}
            }
        )
    in
        Source,
    
    
    DataSAP = 
    let
        Source = #table
        (
            {"Data.SAP"},
            {
                {"DataSAPA"},	{"DataSAPB"}
            }
        )
    in
        Source,
    
    RenamerListForSAPTable = 
    {
        {"Data.SAP", "Data.MES"}
    },

    RenamingSAPTable = Table.RenameColumns
    (
        DataSAP,
        RenamerListForSAPTable
    ),
    Combine = Table.Combine
    (
        {
            DataMES,
            RenamingSAPTable
        }
    )


in
	Combine

The "matching-list" looks like this

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mrbuttons07]

Hi Jimmy,

Thanks for your reponse, your script works fine. I also got into another option whereby I simply merge two columns by creating a new one and then add a separator.

Question is, how can I remove those null values there? I'm unable to move to the next step due to that error. 

Thanks a lot!

[Jimmy801]

Hello @mrbuttons07 

I think that the solution provided is the clearest and the easiest to maintain. 

About your solution...  I didn't quite understand how it works and therefore I didn't get you when you are asking about null-values. 

If you could provide a detail error or problem description I can try to help

All the best

Jimmy

[mrbuttons07]

Hi Jimmy,

Yes, your solution given was actually clearer than mine. It matches both columns that I want easily.

What I meant was merging both columns into one single column. Example: MES Batch merge with SAP Batch where I added a semicolon to separate it. The null values appeared because the source file contains null value, which was entirely my fault for not checking it properly.

Thanks a lot, Jimmy!

[Jimmy801]

Hello @mrbuttons07 

thanks for your feedback. Its very appreciated.

Jimmy