Forum Discussion
Append data from four tables into one new table
Hi,
I have four tables with the same column name and data type. I have to append data from all four tables into one, new.
I can do append data with Append Queries as New options but only with two tables.
When I try to do the action (Append Queries as New) with three or four tables together I got an error:
Apply query changes
Append1
Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union all select
Do you have any idea what is the problem?
Thank you
It sounds like Power Query is trying to fold the queries back to the MySQL source, and MySQL cannot handle a UNION statement with more than 2 tables.
To fix this, break folding. To do this, go to each table you want to append and in the last step, wrap it with Table.Buffer().
So if the last step is something like:Table.AddColumn(#"Changed Type", "Total", each [Cost] + [Tax])Make it
Table.Buffer(Table.AddColumn(#"Changed Type", "Total", each [Cost] + [Tax]))Then Power Query will do the combinations in the mashup engine vs asking the server to do it.
2 Replies
- edhans
Community Champion
It sounds like Power Query is trying to fold the queries back to the MySQL source, and MySQL cannot handle a UNION statement with more than 2 tables.
To fix this, break folding. To do this, go to each table you want to append and in the last step, wrap it with Table.Buffer().
So if the last step is something like:Table.AddColumn(#"Changed Type", "Total", each [Cost] + [Tax])Make it
Table.Buffer(Table.AddColumn(#"Changed Type", "Total", each [Cost] + [Tax]))Then Power Query will do the combinations in the mashup engine vs asking the server to do it.
Thank you Edhans. This solves my problem.
