cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Makah Frequent Visitor
Frequent Visitor

Merge tables but mantain a column with origin field

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

2 REPLIES 2
Moderator v-sihou-msft
Moderator

Re: Merge tables but mantain a column with origin field

@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

Capture4.PNG

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

 

Regards,

Makah Frequent Visitor
Frequent Visitor

Re: Merge tables but mantain a column with origin field

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