Combining Tables from different data sources in Tabular Model

by

I have two different SQL server databases/data sources in my Tabular Model.

I would like to combine/append a table with the exact same design/structure from each source, but I am not sure if that is possible in the Power Query graphical interface.

The graphical interface when does not seem to support this, but I am wondering if it is possible with Advanced Editor/M/Power Query. The append function in the graphical interface does not seem to allow two tables from different sources

The tables have already been added in the model so I need to combined existing tables. I don’t see how you can import tables from two different data sources at the same time in order to combine the two queries in Power Query when busy with Transform Data.

This posting is similar but does only suggests a Calculatedtable function. I would like to use Power Query, because I need to transform the data to amongst other assign new foreign keys/id’s to deal with duplicate values in certain columns once it is combined.

Combine two fact tables from two different marts and model in create Tabular model

Sample Data:

DB’s: Company1DB and Company2DB

Tables: Company1DB\FactData and Company2DB\FactData

Source 1
Company1DB\FactData table:

CompanyName Person Sales
A Peter 100
A Sue 200

Source 2

Company2DB\FactData table:

CompanyName Person Sales

B Peter 300
B Sue 400

Desired Result:

COMBINED TABLE

CompanyName Person Sales

A Peter 100
A Sue 200
B Peter 300
B Sue 400

I know if there’s anything in the graphical interface but you can go into the advanced editor and write the code for it.

let
  Source1 = ...,
  Source2 = ...,
  #”Combined Tables" = Table.Combine({Source1, Source2})
in
  #”Combined Tables"

If you’re new to looking at the actual code behind the UI: The #"variable name with spaces" syntax is just a way to have variable names with spaces.

Leave a Comment