Comments on: How to join tables in Excel: Power Query vs. Merge Tables Wizard

Combining data from multiple tables is one of the most daunting tasks in Excel. If you decide to do it manually, you may spend hours only to find out that you've messed up important information. The good news for all Excel users - Power Query or Merge Tables Wizard can be your time-saver. Continue reading →

Table of contents

Comments page 2. Total comments: 51

  1. Thank you very much for a clear tutorial!!!!!
    Could you provide me following information:

    What if in "datasource A" and "datasource B" the names are not the same:
    Example:
    Datasource A:
    user1 - 10 - 5
    user2 - 5 - 7
    user3 - 6 - 8
    user4 -5 -9
    user7 - 6 -10

    Datasource B
    user1 - 10 - 5
    user2 - 5 - 7
    user3 - 6 - 8
    user9 -5 -9
    user10 - 6 -10

    I would like to use this merge function as a grading / evaluation tool for my students. So I would also need to have the data that is only available in datasource A and datasource B merged. I successfully merged data from students that are in both datasources, but I was still unsuccessful where a student is only there in one datasource.

    Result would be:

    user1 - 10 - 5 - 10 - 5
    user2 - 5 - 7 - 5 - 7
    user3 - 6 - 8 - 6 - 8
    user4 -5 -9 - 5 - 9
    user7 - 6 -10- null - null
    user9 - null - null - 9 - 10
    user10 - null - null - 6 - 10

    Thank you very much for helping me out.

  2. Dear Experts,

    I have a question:
    I have 70+ tables in dbf with columns: year, product, value. Tables have different years data! To make join.
    EXAMPLE.
    Table 1
    Year product value
    1993 Apple 98.45
    1994 Mushrooms 67.54

    Table 2
    Year product value
    1992 Apple 95.45
    2021 Melon 112.0

    I need a pivot table(to consolidate) all tables in one table.

    My way:
    Let
    DatesList={1994...2021},

    Tbl=Query.odbc("dsn...", "select * from c:\data\1993.dbf"),

    Result=List.Accumulate (DatesList, (state, current) =>Table.Combine(Tbl, "product", Query.odbc("dsn...", "select * from c:\data\" +Text.From(current) +".dbf", "product")

    in
    Result

    Its ok, but results only for the last date. How to save Table between dates

    Please, help

    1. Hello!
      If I understand your problem correctly, you can convert all dbf files to xlsx using Excel. Then you can use Ablebits Data - Copy Sheets or Combine Sheets to combine all files into one. Then use a filter to remove unnecessary data.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      Hope you’ll find this information helpful.

  3. Is there a way to update a history table anytime my table that is being updated every 5 minutes changes? I have a table that is updated every 5 minutes from a MySQL database. It consists of 7 columns. Date time is in the first column and the rest are equipment and location of the equipment (Longitude/Latitude)

    So it is only one line, but overwrites itself when it updates. I would like to be able to keep a running history of whenever the data changes. It could be whenever time changes every 5 minutes or whatever update rate I choose, but better would be if it updated whenever the longitude or latitude changed. Latitude is in the 4th column, Longitude is in the 5th column.

    Is there a way to make a history table like this that is linked to the live data table and only updates when data changes by a certain amount and appends the new data, adding a new line of the table so we can look back and see the history of when the equipment moved?

  4. Exactly what I needed.
    Well written and extremely helpful thanks!

  5. I have common field. But content in sheet 2 has some extra rows ,also some rows missing
    I want inter section of these two sheets. Help.

  6. Hi. I'm trying to merge two tables with the following format:

    Table A:
    ID Attribute1
    1 A
    2 B
    3 C
    4 A

    Table B:
    Attribute1 Attribute2
    A XYZ
    B WWC
    C ABC

    The merge is done on "Attribute1". Everything works fine for the first match, but then the second one (ID = 4) is left blank. What I would like is a true join. Is this possible? Thanks!

    1. Hello Kevin!
      After Merge Tables Wizard you can sort your new table by the "Attributes1" column. Then use the "Fill blank cells" tool (Fill cells downwards) in the "Attributes2" column to fill the blank cells.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  7. how do i combine tables with information of sales of different days into one table spread on one excel sheet

  8. Thanks. Very helpful and clear. Worked a treat.

  9. Using Excel 2016... any idea why the 'Join Kind' dropdown would be missing from my 'Merge' tab? The ONLY option I have at bottom of this tab is a check box to "only include matching rows".

    1. Hello!
      A drop-down list is a control that allows you to enter data in cells of your table. The Merge operation can be performed only with data from an Excel table, controls are not transferred to another table during the merge.

  10. Dear all

    I am having a problem in merging 3 tables.
    The Primary key is product code, one to many situations.
    I use Leftouter join
    Table 1 - code /product
    Table 2 - code/Sales
    Table 3 code/price
    Code /PRODUCT / SALES /PRICE
    AA /Product
    AA /Product / Sales
    BB/ / Sales /Price
    CC/Product / /Price
    Whenever, same code for other table, it repeat itself on the 1st Table
    However, can I make them not to repeat?
    Eric

  11. Thank you very much. This is very helpful.

  12. Very well detailed.
    Is there a way to duplicate what we can do with PBI, hence not to merge any tables but to create instead connections (1 to many) thus allowing us to create a pivot table to extract the data of our choosing?

  13. Power Query not supported on Mac with excel files

  14. I have an (Excel)lent dilemma.

    I'd like to use Merge Queries as New, but it does not show up in Office 365 at work.
    The Merge Queries as New option in Merge Queries drop-down is not available.
    For days, I could not figure out. Even IT guys gave up.
    I would make my Excel work a lot easier.
    Any help is appreciated.

    David

  15. Supercool, I had no idea about the tool. Now it saves me a lot of time whenever I am too lazy to use SQL. Thanks for the post.

  16. I can't see Join Kind in excel 2016, Please.

  17. Hi Svetlana
    Thanks for this great post. I have 3 worksheets (Tables) that I want to join in one worksheet (Table). I tried it as you described using power query, but at final step I get following error:
    "Initialization of the data source failed.
    Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database."

    There isn't any external database! All tables in the one file (workbook).
    Could you please advise me.
    Thanks in advance.

  18. Other way:
    Note: I use Power Query (M Language)
    In this example I have two tables and this function (myfunction)
    cuentas(id,label,systype)
    datos(periodo,cuenta,monto)
    (myfunction)=>

    let
    Source = Table.NestedJoin(datos,{"cuenta"},cuentas,{"ID"},"unionall",JoinKind.LeftOuter),
    #"Expanded unionall" = Table.ExpandTableColumn(Source, "unionall", {"LABEL", "SYSTYPE"}, {"LABEL", "SYSTYPE"})
    in
    #"Expanded unionall"

    Then when I use "myfunction", I generate one table whit two additional colums, one with the label and other with account type for every cuenta in the table datos.
    "ID" is unique over the table "cuentas", and cuentas have many entries in the transactional table (datos) in the field "cuenta".

    Regards from Maracaibo, Venezuela

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)