Help with table relationships, several tables per year

[Anonymous]

Hi everyone.

I am pretty new to Power Bi and dont know how to proceed with the following:

I have 5 different tables for the past five years that include a column for Suppliers and another column for the country of each plant we procure from from each supplier like below:

2015
Supplier	Country
A	Z
A	Y
A	X
B	Z
B	Z
C	J

2016
Supplier	Country
A	Z
A	Y
B	X
B	X
B	J
C	J

The suppliers vary among the 5 year period (there are new suppliers as well as others that dont show up from year to year) and the occurrence of them also varies (eg. we might procure from 5 plants one year and the next from 9)

I want to be able to create a report that shows the variations in ocurrence along the 5 year period for supplier/country as well as visualize the new suppliers and those that stop showing up in the records during the same period.

Any guidance will ber very much appreciated.

Thanks in advacne

[d_gosbell · Accepted solution]

Probably the best approach is to add a "Year" column to each of your 5 tables with the appropriate value, then do an Append Merge operation to append all 5 tables into 1 table (you would then turn off data loading for the 5 original tables to avoid loading all the data twice), then it would be much easier to write generic measures that will do comparisons across the various years. 

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create new calculated table using DAX below.

Table = UNION(ADDCOLUMNS('2015',"Year","2015"),ADDCOLUMNS('2016',"Year","2016"))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[d_gosbell]

---

@v-xicai wrote:

Hi @Anonymous ,

You can create new calculated table using DAX below.

Table = UNION(ADDCOLUMNS('2015',"Year","2015"),ADDCOLUMNS('2016',"Year","2016"))

---

You can do that. But then you have all the data loaded into memory twice. It's a better practice to do the unioning in Power Query and only have one copy loaded into memory.

[d_gosbell · Accepted solution]

Probably the best approach is to add a "Year" column to each of your 5 tables with the appropriate value, then do an Append Merge operation to append all 5 tables into 1 table (you would then turn off data loading for the 5 original tables to avoid loading all the data twice), then it would be much easier to write generic measures that will do comparisons across the various years.