This post is also available in: Deutsch
If you want to combine several tables, you have to ask yourself whether you should do this already in the data source such as SQL Server, in the processing of the data (ETL) in Power Query or in Power BI and, above all, where exactly there is a difference.
First of all, the post is relatively detailed, for all of those who consider it too long (tl;dr) here is the comparison table:
|Duplicate entries remain||Duplicate entries are removed||Unequal number of columns||Deviating column names||Different order of the columns||Combine more than 2 tables|
|UNION in SQL Server||With UNION ALL||With UNION||–||x||–||x|
|APPEND in Power Query||x||Possible with “Remove duplicates”||x||–||x||x|
|UNION in DAX||x||Possible with “DISTINCT”||–||x||–||x|
Data transformation in the data source or in the frontend?
A question that