Combining rows based on two or more columns

[stfnzmmrmnn]

Hi all, I am looking for a (easy) way to combine rows by some key vallues.

It's best explained by an example:

id	timezone	temperature	longitude	latitude
1	Berlin	23	null	null
1	Berlin	0	7000000	8000000
2	Berlin	30	null	null
2	Berlin	0	6000000	5000000
2	New York	0	null	null
3	Spain	33	null	null
3	Spain	0	4000000	5500000

I am looking for a way to combine the rows by more than one key value (in this example only if id AND timezone is equal).

The result would be like this:

id	timezone	temperature	longitude	latitude
1	Berlin	23	7000000	8000000
2	Berlin	30	6000000	5000000
2	New York	0	null	null
3	Spain	33	4000000	5500000

I appreciate any help I can get!

Thank you very much!

Cheers

Stef

[Zubair_Muhammad · Accepted solution]

Hi @stfnzmmrmnn

Try creating a Calculated Table from Modelling Tab>>>New Table

New Table =
SUMMARIZE (
    Table1,
    Table1[id],
    Table1[timezone],
    "temperature", SUM ( Table1[temperature] ),
    "Longitude", SUM ( Table1[longitude] ),
    "Latitude", SUM ( Table1[latitude] )
)

[Ashish_Mathur · Accepted solution]

Hi,

You can do this in the Query Editor.  Select the first two headings and right click > Group.  Select the SUM function there for the remaining three columns.

Hope this helps.

[Zubair_Muhammad · Accepted solution]

Hi @stfnzmmrmnn

Try creating a Calculated Table from Modelling Tab>>>New Table

New Table =
SUMMARIZE (
    Table1,
    Table1[id],
    Table1[timezone],
    "temperature", SUM ( Table1[temperature] ),
    "Longitude", SUM ( Table1[longitude] ),
    "Latitude", SUM ( Table1[latitude] )
)