Power BI: merging tables in Power Query
There are two options to combine queries – the #append and #merge functions in #PowerQuery.
Merging queries is based on matching rows rather than columns. When merging tables, you are joining them side-by-side using a matching column.
Merge is often compared to #Excel #VLOOKUP and some may wonder why bother using it. However, the merge function is much more powerful and has better functionality than the good old VLOOKUP.
First and foremost, Power Query can work directly with external data sources. This means when working large amounts of data, stored in databases, CSV and web queries, you can bypass Excel and jump straight into Power BI.
Second, the merge can lookup based on multiple conditions.
Third, when there is a need to compare lists from two tables, where some values are in both tables while others are not. A simple merge using only the matching rows from the two tables (inner-join kind) will get the job done quickly.
Finally, the merge function can return multiple columns with one query.