combining 2 rows into 1 by ID

[data123456789]

Hi everybody,

i am searching the forum the whole day but couldnt find a solution which fits.

i am looking at following table

ID             time              A            B             C

1234          xxx               5            10           null

1234          xxx              null        null           100

I already used pivot to get this far so unpivoting doesnt work

final result should be

ID             time              A            B             C

1234          xxx               5            10           100

Thank you for your help

[Greg_Deckler · Accepted solution]

@data123456789 - In Power Query editor you should be able to use Group By

[AlB · Accepted solution]

Hi @data123456789 

Use Group By. Make sure that what you show as "null" in your table is actually of type null in the table (and not the string null)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUaqoqACSpkBsaAAklGJ10KQgyNDAQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Time = _t, A = _t, B = _t, C = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ID", "Time"}, {{"A", each List.Max([A]), type text}, {"B", each List.Max([B]), type text},{"C", each List.Max([C]), type text}})
in
    #"Grouped Rows"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[v-lili6-msft · Accepted solution]

HI @data123456789 

You could get it by use Group By in Edit Queries:

https://support.microsoft.com/en-us/office/group-rows-in-a-table-power-query-e1b9e916-6fcc-40bf-a6e8...

Result:

Regards,

Lin

[AlB · Accepted solution]

Hi @data123456789 

Use Group By. Make sure that what you show as "null" in your table is actually of type null in the table (and not the string null)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUaqoqACSpkBsaAAklGJ10KQgyNDAQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Time = _t, A = _t, B = _t, C = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ID", "Time"}, {{"A", each List.Max([A]), type text}, {"B", each List.Max([B]), type text},{"C", each List.Max([C]), type text}})
in
    #"Grouped Rows"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Greg_Deckler · Accepted solution]

@data123456789 - In Power Query editor you should be able to use Group By