4

I have data coming in a certain way, but I need it automatically converted into a different layout for a specific chart in Google Data Studio.

Basically, I want to go from this

Date    Apple   Banana   Cherry

Jan 1   500     800      2000
Jan 2   800     920      2878
Jan 3   1700    1850     3000

To this:

Date     Type     Amount

Jan 1    Apple    500
Jan 1    Banana   800 
Jan 1    Cherry   2000
Jan 2    Apple    800
Jan 2    Banana   920
Jan 2    Cherry   2878
Jan 3    Apple    1700
Jan 3    Banana   1850
Jan 3    Cherry   3000

I can't think of a solution (I'm relatively new at this). I'm hoping someone might be able to point me in the right direction. Thanks in advance for any help I can get!

player0
  • 124,011
  • 12
  • 67
  • 124
David Folkerson
  • 43
  • 2

2 Answers2

6

ultimate 720° freedom with no limits:

=ARRAYFORMULA({"Date", "Type", "Amount"; 
 SPLIT(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B2:Z<>"", A2:A&"♠"&B1:1&"♠"&B2:Z&"♦", )), , 500000)), , 500000)), "♦")), "♠")})

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Oh man this looks amazing and I'm super excited to try it out! Before I get started on it, what do the spades and diamonds mean in the formula? Are they just meant to make the formula more legible, or do they actually stand for something? Sorry if this is a dumb question. I googled it but couldn't find anything. But if my guess is correct, then that's a really neat technique that I would totally start using myself. – David Folkerson Apr 01 '19 at 09:54
  • they mean kinda nothing, it's just unique symbol to join data with so in later steps we could split data on them and eliminate them from the final result. – player0 Apr 01 '19 at 12:13
  • Hey player0 - just wanted to say I tried your method out and it works like a charm! You're a genius. Thank you so much! Should I pay attention to the 500000 in your formula for anything? Is there a limit to the amount of data your formula can parse, similar to Tom's example above? – David Folkerson Apr 02 '19 at 00:51
  • the only "limit" is those two 500000 so if you ever hit that just increase it with zeros like 5000000000 or just change it to: 99^999. Tom uses TEXTJOIN method witch is limited to join no more than 50,000 characters. this method can bypass this limit – player0 Apr 02 '19 at 00:57
  • @DavidFolkerson if any of the provided answers helped to soleve your issues pls mark the answer as solved by accepting it and upvote all usefull answers – player0 Apr 02 '19 at 01:03
  • marked it as solved. Thanks for letting me know I needed to do that. Can't upvote it though because I don't have enough reputation points :( – David Folkerson Apr 02 '19 at 12:29
2

If you haven't got too many rows and columns, you can use a split/join approach:

=ArrayFormula({"Date","Type","Amount";split(transpose(split(textjoin("♫",,A2:A4&"♪"&B1:D1&"♪"&B2:D4),"♫")),"♪")})

Because this combines the data into a single cell before splitting it, there is a limit of 50,000 characters on the data plus separator characters.

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Thanks, Tom! The limits worry me though. I'm going to need to be able to handle a few years worth of data, which brings me close to the 50,000 character limit. Can I ask what the music notes mean in the formula? This is my first time seeing special characters like that. Thanks! – David Folkerson Apr 01 '19 at 10:00
  • Yes that is the downside of my relatively simple approach. I haven't had time to reverse-engineer Player0's solution to see how that works, but anything that overcomes the 50.000 character limit is an excellent achievement. The musical notes are just an arbitrary choice of character that's unlikely to crop up in your data - ♪ is the separator used to split data into separate columns and ♫ to split into rows. – Tom Sharpe Apr 01 '19 at 10:27
  • Hi Tom, thanks for explaining that to me. It's a neat little trick that I'm sure will come in handy in the future. So far player0's formula is working, so I'm going with that for now. I've asked about the 500000 in the formula, not sure if there is a limit that I need to be aware of. – David Folkerson Apr 02 '19 at 00:52
  • @DavidFolkerson 500000 in my solution does not mean 500000 characters but 500000 rows/cells of data – player0 Apr 02 '19 at 01:07
  • There are more answers to what I think is the same question here https://stackoverflow.com/questions/24954722/how-do-you-create-a-reverse-pivot-in-google-sheets/55503908#55503908 – Tom Sharpe Apr 03 '19 at 20:51