Merge tables but mantain a column with origin field

[Makah]

I have one Excel for each store that I have (store 1.xls; store 2.xls; store 3.xls)

I have imported every file into BI in a diferent table because some stores have more information that another, for example: observation field

To consolidate all store into one table

1) I have created an Index like: [date] & "#" & [productID]

2) Then create a new Table that consolidate with DAX : FILTER(DISTINCT(UNION(DISTINCT(store1[INDEX_DATE_PRODUCT]);DISTINCT(store2[INDEX_DATE_PRODUCT])));NOT(ISBLANK([INDEX_DATE_PRODUCT])))

3) Then create a new column with DAX: CALCULATE(SUM(...);FILTER(...))

4) Then split the Index with DAX: PATHITEM(SUBSTITUTE(summary[INDEX_DATE_PRODUCT];"#";"|");1)

Question¹: There is a way to do this with less steps or smarter?

Main question: I have an unique ProductID from each store. How can I have a column with an origin destionation table.

Example merged table:

| INDEX_DATE_PRODUCT | SUM_QUANTITY | DATE | PRODUCT_ID | ORIGIN

| .... | 100.000 | 2015-01-05 | axb001241 | store1

| .... | 150.000 | 2015-01-05 | axb0a1241 | store2

| .... | 280.000 | 2015-01-05 | aab001000 | store1

[v-sihou-msft]

@Makah

In this scenario, firstly I suggest you adding calculated column or removing extra column to keep same structure of all three dataset. Then you can just go to Query Editor and use "Append Queries" to combing all three tables together. Please see: Shape and combine data in Power BI Desktop

After that, you do modeling like adding Index column on this combined dataset.

Regards,

[Makah]

Thanks for your reply @v-sihou-msft. But how can I get a column with info about the origin table, like a destination table?