Blend two google sheets data with date range

[Kaveh]

Hello,
I have two sheets with same columns, one has the old data and the other has the new data. 

I have some clients email and phone and a date/time column that is in a standard format. I also have the channel column that represents the marketing channel for that lead. 

I want to have the record count of both sheets with a filter condition ( channel equals G-ADs ) , so I made a blend data source with both sheets with "Full Outer" condition and applied the "channel equals G-ADs" filter on both sheets datasources,  and have the column "email" as the Join condition. (I don't know if this is right or not) 
I may have some repetitive rows in the sheets , and it can be detected by the emails column. 

I want to have the record count of both sheets, without any duplicated rows. 

So I create a card and selected the blend data source. then as the metric I created a new filed and insert this formula:
Record Count (All Leads - old sheet) + Record Count (All Leads - new sheet)
But I do not get the number that I expect, and when I change the report sheet global date, it does not affect the number that I get. 

Can someone help me solve this problem?
Many thanks. 

[sam8 · Accepted solution]

It sounds like you don't really want to join these tables together. More accurately, you want to stack one on top of the other, right? Or combine them, UNION them, etc? 

Looker Studio's join/blend functionality doesn't support unions. I'd recommend upvoting an existing feature request. There is a workaround that I think some folks have had success with (I haven't tested it myself) in the article How to mimic UNION ALL operator with Looker Studio and combine sets of data.

[sam8]

This is the specific feature request I'm thinking of: https://issuetracker.google.com/issues/119886983

[Kaveh]

Hi Sam, 

Thanks for the info. I got your point. You're right. 

I think It's better to get those sheets together in the Google Sheets and then use the new sheet as the data source.  

PS: I voted for the feature 🙂 It's weired that we don't have that option 😉

Thanks again.

[sam8]

Agreed 😁 

Good call on combining the tables together in Sheets, that's a lot easier!