Combine Rows only keeping unique values

[memote1]

Hello,

I am trying to combine a table so I only have

• 1 row per PARTICIPANT_ID
• No duplicate dates per PARTICIPANT_ID
• Dates are sorted chronologically per PARTICIPANT_ID
• All PARTICIPANT_ID rows have at least 1 date (some rows are blank, and some people have multiple blank rows)

This is what my table looks like now:

PARTICIPANT_ID	DOSE_ADMIN_DATE	RCV_ELSEWHERE_1ST_DOSE	RCV_ELSEWHERE_2ND_DOSE	RCV_ELSEWHERE_MEASLES_DOSE
18518506	2/1/1978			3/21/1975
16688509		5/22/2014
16688509			6/26/2014	6/26/2014
16688509	7/31/2019	7/31/2019
16688509	11/3/2019	7/31/2019	11/3/2019
1055836
1055836	4/26/2016
1055836	7/12/2016
1058095	10/31/2005	10/31/2005
1058095	3/4/2016
1058095	3/4/2016
1058270
1058270	4/17/2006	4/17/2006

And this is what I would like it to look like:

PARTICIPANT_ID	DOSE_ADMIN_DATE	RCV_ELSEWHERE_1ST_DOSE	RCV_ELSEWHERE_2ND_DOSE	RCV_ELSEWHERE_MEASLES_DOSE
18518506	3/21/1975	2/1/1978
16688509	5/22/2014	6/26/2014	7/31/2019	11/3/2019

Thank you!

[V-yubandi-msft]

Hi @memote1 ,

No need to worry about being new to Power Query it’s absolutely fine. Let me break down @Akash_Varuna  solution for you step by step. Thanks for your inputs @Akash_Varuna .

Steps :

1. Open the Power Query Editor (Transform Data).
2. Select the PARTICIPANT_ID column.
3. Navigate to Transform  Group By.
4. In the Group By window.
5. For the new column name, leave it as All Data.
6. Set the operation to All Rows (not Count).
7. Click OK.

How to GROUP BY or summarize rows - Power Query | Microsoft Learn

After that Click on the small expand icon  next to the new column. From the list, select all the columns except PARTICIPANT_ID. Then, click OK to expand the data back into the table..

Select each date column one by one. Then, choose the Sort Ascending option to arrange the dates from oldest to newest.).

Go to Transform  Merge Columns. You can use a comma as a separator if needed.

If required, create a Custom Column to combine multiple date columns into a single column.

I hope this proves to be helpful……

[memote1]

@V-yubandi-msft Thank you for walking me through the steps!

[dufoq3 · Accepted solution]

Hi @memote1, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY/RDcMgDERXifiOdLbBBmaJsv8ahcRtI0pSCUuH79kc2xa4aDtkYQ0CBtdcmly8IuToadjXxpqVxlY3FSIQ4vQZmEC9DGJv8KsHOCNyN0Y93cyMOIOvfR8i1RLtkmaZeMlT2QOTwXLLFKra36czDP1cZnRE+rfwEZFMd/9yL4FzT2CDdnZ/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PARTICIPANT_ID = _t, DOSE_ADMIN_DATE = _t, RCV_ELSEWHERE_1ST_DOSE = _t, RCV_ELSEWHERE_2ND_DOSE = _t, RCV_ELSEWHERE_MEASLES_DOSE = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"PARTICIPANT_ID", Int64.Type}, {"DOSE_ADMIN_DATE", type date}, {"RCV_ELSEWHERE_1ST_DOSE", type date}, {"RCV_ELSEWHERE_2ND_DOSE", type date}, {"RCV_ELSEWHERE_MEASLES_DOSE", type date}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"PARTICIPANT_ID"}, {{"T", each {[PARTICIPANT_ID]{0}} & List.Sort(List.Distinct(List.RemoveNulls(List.Combine(List.Skip(Table.ToColumns(_)))))), type table}}),
    Transformed = Table.FromList(GroupedRows[T], (x)=> x, Value.Type(Table.FirstN(ChangedType, 0)))
in
    Transformed

[memote1]

@dufoq3 

It might be my company's firewalls, but I am unable to access your link for how to use your query.

When I copy the query exactly, I get 5 rows, when I should have > 16000 in my real data. 

I've also tried copying everything from the second "let", and it is telling me "Token Identifier expexcted"

I realized something that might also be affecting it, I have 4 columns between Participant ID and Dose Admin Date. I'm used to working in Excel, and am new to PQ and am starting to realize the impact changes in columns has on M language, so I apologize if that is an issue.

Thank you!

[dufoq3]

[memote1]

@dufoq3 

Thank you for the solution! 

Can you explain to me this portion of the Table.Group function? I think it's the "aggregatedColumns as list" component combined with the List functions. Thank you!

{{"T", each {[PARTICIPANT_ID]{0}}

[dufoq3]

Hi @memote1,

{[PARTICIPANT_ID]{0}}

regarding PowerQuery is zero-based so {0} means first value fro the list. So code above means get first value from the list [PARTICIPANT_ID] and I put it into curly brackets because I wantet to have that extracted value in the list. So output of that code in such Table.Group is just PARTICIPANT_ID 

[memote1]

Thank you!

[dufoq3]

You're welcome.

[AntrikshSharma]

@memote1 For participant ID 16688509 why would "5/22/2014" come under the column "DOSE_ADMIN_DATE" when in the Original table it belongs to the column "RCV_ELSEWHERE_1ST_DOSE"

[memote1]

Because it was the first dose given. It doesn't matter if it was given at my facility (indicated by "DOSE_ADMIN_DATE" or they brought us documentation ("RCV_ELSEHWERE....").

[Akash_Varuna]

Hi @memote1 Try these please 

1. Group by PARTICIPANT_ID: Use Power Query's Group By to group rows by PARTICIPANT_ID.

2. Deduplicate Dates: Remove duplicate and blank dates in each column.

3. Sort Dates: Arrange dates chronologically for each column.

4. Combine Rows: Concatenate the cleaned, sorted data into one row per PARTICIPANT_ID.

[memote1]

@Akash_Varuna 

I am new to PQ, and I don't know how to do most of what you suggest, particularly the first step. When I tried, My table converted to 1 column with Particpant ID and another with Count. 😕