Merging multiple tables when not all individual rows are present in each table

[rafael_sa]

Hi folks, 

I have a bunch of excel files for each month of the year. Each excel sheet in this file has some values/columns about some projects(public selections).

Information on each sheet is agreggated by project and year.

I have imported each sheet as a table and now i want to join.

To be more specific I'll share a screen of the contents.

What a i have is:

sheet 1 has this content

sheet 2 this content

sheet3 this content

As you can see not all "chamada pública" are listed on each sheet, but a have created a unique identifier, that is related to the tuple ( "chamada publica", ano).

what I want to achieve is getting one unique table that has all the columns from all the sheets, but only one row for each tuple( chamada publica,ano,data_posição), like this:

 As I mentioned before, i have imported each required sheet and then merged it with a table with all possible values for the tuple( chamada publica,ano), creating so a column named ID_CHAMADA. Hence, all the tables that i want to merge and get the columns has this identifier:

How can i achieve that in powerbi ?

[V-lianl-msft · Accepted solution]

Hi @rafael_sa ,

Based on your description, do you want to get the field in each table and append it into a new table after removing the duplicate?

Please refer to the sample:

Table 2 = DISTINCT(UNION(DISTINCT('Table'[Column1]),DISTINCT('Table (2)'[Column1]),DISTINCT('Table (3)'[Column1])))

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

[PaulDBrown]

You need to create dimension tables for all the fields common to both tables (including a date table for the date field) with unique values covering the range of values in each field. You then use the dimension fields to create the visuals, slicers, filters and in measures.