Google Sheets - QUERY Rename Columns Using Label & Format Results Tutorial - Part 9

Learn how to rename columns using label clause in Google Sheets QUERY & format results as number, currency, different date types, rename and format multiple columns and much more.

[CAT_IN_BOX]

I binge your query videos list. Really helpful for me. You're the best!

[lazalazarevic]

Best video on Query function available on YT. Thank you man

[Pettelwood]

Wow, easy to understand, easy to refer back to. Great job

[emmakatejohansen]

was struggling with compstat homework! THANK YOU!!!!

[farmarcos]

Why haven't used the 'where condition' in the first 'query'? 16:32

[jsemashko]

Hello!
Thank you for video.
I've a question about "options no_values" clause. Could you help me? I have 2 very large tables with several thousands of rows and only 2 columns each. First column is 'datetime' and the second is some parameter.
In the table A datetime had been recorded each 5 minutes, and in table B – each 20 minutes. I want to merge these tables into one with datetime every 1 hour.. Something like this:

Datetime (YYYY-MM-DD HH:00:00) AVG(ParameterA) AVG(ParameterB)
So what’s am I doing?
1. Getting data from table A with query:
Select MIN(A), AVG(C), Year(A), Month(A)+1, Day(A), Hour(A)
group by Year(A), Month(A)+1, Day(A), Hour(A) Order by MIN(A)
Format MIN(A) 'YYYY.MM.DD HH:00:00'
2. It's give me exactly that I need! Every row have been grouped by hour and everything seems to be ok.
3. Than I’m getting data from Table B with the pretty much the same query.
4. And after all I’m trying to merge data by VLOOKUP (hour-by-hour).
5. And here comes the problem! Although my datatime columns looks very nice and clean (because of format) it has different Min(A) values, because data was written in different minutes of hour.
So I have cells in Tables A&B looks like “2017.06.15 01:00:00”, but if I’m checking the values of these cells, it is “2017.06.15 01:04:00” and “2017.06.15 01:09:00” for tables A and B. And because the values is different, VLOOKUP is not working. (
6. I’ve found information about “options no_values” clause, but it is not changing anything in my case… What I’m doing wrong?
Thank you!

[cherrepeltier]

Are there commands to format columns left, center or right justified and the Header bold, centered, etc?

[MelissaWright-wo]

Hi Very helpful, thank you! I have an issue where I am using query to pull names from one table into a secondary table but when a new name is added to the first table and it pulls through to the secondary table, it doesnt reconise the new rows and doesnt auto format? Why is this? Can I fix it?

[arfanariyanto]

any idea to rename query pivot label ? i still finding how to rename header label from the result query pivot

[ronyjoseph]

@Learn Google Spreadsheets
How to have total sum below the transactions? Also is it possible to have sub-total below the transactions called using query

[vinidamasio]

Please how could I add a new column that not exist in the data source ?

[nick_g]

Awesome Playlist. I've had so many "AHAA!" moments watching your courses. Excellent! I have a problem where I want to report the ONLY the most profitable sales number out of 3 categories of items, made by 10 different brands, all of which have sales in the same set of 20 cities. So for Los Angeles maybe NIKE's most profitable sales category is shoes at $3m and Ralph Lauren's most profitable sales category is hats at $1.2m, but in Dallas NIKE's most profitable category is shirts $2m and Ralph Lauren's most profitable sales category is shoe's at $4m. I could make a report that shows all of the categories and just visually locate the most profitable one, but I want to only show the most profitable one and ignore the others. The brands would have different categories for each city because the demographics are different. Any guidance is appreciated. I'd like to avoid writing a script with some kind of loop.

[iqdx]

At 6:58 you show YEAR(A) parameter and get what you want. When I try QUERY fails (#VALUE!) and I am told A does not have a DATE or DATETIME format. But it does: I checked. And regular function =YEAR(A) works fine in my sheet. What am I missing?

[maddon]

Hi. Great content btw. Please keep it coming :)
My question.
=QUERY('Booking Sheet'!$A$3:$F, "select B where D='"&E14 & "' limit 2", 0)

Let's say you have set your display results limit to 2 because you know in some cases there is more than one match. How do I get the results to display horizontally in the next cells instead of below each other?


I'm asking you because I actually had something like this a few years ago but can not for the life of me remember how I did it.


Your help would be greatly appreciated.

[AlexandreCaruso]

Hi! I'm from Brazil and love your videos. Use query function and pivot, how could I calculate a total for each line? tks again!

[Paulobrrs]

Hello. I trying some SQL like 'SELECT A, B/100, 1 GROUP BY A', and "100, 1" is a number in a cell. The result is 3 collumns. First is A, the second is "quotient(sum xxx())" and the third is "yy()" where yy is the decimal part of the B/100, 1... I solve using 'B/" & TRUNC(100, 1) & ' ...not the best.

[lapettatraditionalstreetfo]

hello i have a problem labeling a column header with a Date teken from a cell data validation, the result is a number and not a year this is my code =QUERY(TB_vendite;"SELECT sum(I) where month(A)+1 ="&MESE($B$3)&" and year(A)="&ANNO($B$2)&" group by M label sum(I) '"&($B$2)&"'")

where b2 is the cell data validation....the data validation contain 2023 but the label return 44563 i tryied to format by nothing...helpe me please

[santiagodossantos]

Start going back in the playlist to like those that you missed, you'll be greeted by his Ok so in this video phrase like 10 times

[jbgarate]

Buen video. Pregunta como puedo hacer 2 filtros en el query, tengo que filtrar por fecha y por letra A, H, J, K, P, S, R, Q WHERE E='"&B2&"' AND Q>1 ORDER BY A, S";-1)

[hitendrakumarlaxmanbhaicha]

Can you just help me to correct my Formula, I am getting error when i am putting functions of label
=query(A8:AG, "Select A, J, D, U, G, I, K, R, Count(Z), Count(AE), Count(AG), Count(Z)-Count(AE), Count(Z)-Count(AG) where X='SW' group by A, J, D, U, G, I, K, R, lable Count(Z)-Count(AE) 'Jt Count Fitup Bal "', 1)