Forum Discussion

rafael_sa's avatar
rafael_sa
Frequent Visitor
4 years ago
Solved

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

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 ?

Need Help
Show and Tell

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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. 

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.