Transposing two sources duplicates a value

MaryB

Newbie here...

I have two data sources.

One excel table that has 3 rows - a row for each region and a value in 12 columns (one for each month). Pretty simple. I've transposed this to have a region column and one date column and a value column.

Region A     August     10

Region A     September    7

etc.

The second excel sheet has many rows but still simple. There is a column for region, project name, date and a value. Rows are per project.

Region A    Project Alpha      300      August

Region A    Project Beta        400      August

When I join the two I keep duplicating the value in my first sheet because it is grouping by the project name. Thus it will look like this:

Region A   August        Project Alpha           10 (from sheet 1)    300 (from sheet 2)

Region A   August        Project Beta             10 (from sheet 1)    400 (from sheet 2)

Thus, when I want to just look at August and the value from sheet 1, it will show 20 and not 10 as it should.

Is there a way to not duplicate this value?

MaryB

I was able to resolve this using the Union module instead of the Join module as it didn't create extra rows and it put "nulls" in the right place. Then I transposed my measure columns into one column and it took correctly.

Thanks for the help everyone.