CODEWITHSUNDEEP
google-sheetsmergelooker-studio
MrG34r
MrG34r

Reputation: 35

Google data studio - Use multiple datasheet with same data keys/headers

So I've been stuck in this for some days, tryed a lot of search terms but all of them seems to bring me the same answers and i really need this:

I have a demand to join two different company's datas from the same owner, all of them have the same data sources (excel data sheets from FB ADS).

So they all share the same (keys/headers), like this:

COMPANY(1)'S ADS DATA

COMPANY(1)'S ADS DATA

COMPANY(2)'S ADS DATA

COMPANY(2)'S ADS DATA

So this way I need to put then togheter without having to join both of then on excel every time and also give him some nice data manipulation power.

The results should be something like this

Joint data

Joint chart

By now I was trying to join data from the two companys but I couldn't really figure out how to properly do this so far I've made some tests and tryed reading a couple of articles and google data studio's help files. The merging data function seems to mess everything.

data studio merge

As a result of this merge, GDS gives me this fields:

enter image description here

Shouldn't I see like only one field labeled as cnt and cmp? I've noticed that GDS creates not one, but two data fields. If I try adding all data I need as key the left sheet turns all "0s". What Am I doing wrong here?

Upvotes: 0

Views: 2131

Answers (1)

Danalyser
Danalyser

Reputation: 56

I have read your descriptions. It seems that you are looking for a solution to append both tables instead of merging the tables.

Do note that the data blending in GDS is a left outer join.

Hence, instead of doing the blending in GDS, I'd suggest you to append both datasets in Google Sheet in a separate tab before importing to GDS for visualisation. (assuming you don't mind copy-pasting the data into the Google Sheet).

Here is the formula to append both datasets in Google Sheets:

= {QUERY(A!A1:D1000,"SELECT A,B,C,D WHERE A <> ''",1);QUERY(B!A2:D, "SELECT A,B,C,D WHERE A <> '' ")}

I've created some dummy data in this google sheets and appended the data using the formula provided , you may take a look to understand further.

If you are unclear on the difference between merge and append, you may take a look in the Google Sheet documentation as well.

On a side note, I've screencast the process of answering this question and posted on my youtube channel. You may take a look if needed. (Thanks for the question and inspiration you provided for the video)

Upvotes: 2

Related Questions