Forum Discussion
Helper ICombine Rows only keeping unique values
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!
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
13 Replies
- Akash_Varuna
Super User
Hi memote1 Try these please
Group by PARTICIPANT_ID: Use Power Query's Group By to group rows by PARTICIPANT_ID.
Deduplicate Dates: Remove duplicate and blank dates in each column.
Sort Dates: Arrange dates chronologically for each column.
Combine Rows: Concatenate the cleaned, sorted data into one row per PARTICIPANT_ID.
- memote1
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. 😕
- AntrikshSharma
Community Champion
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....").
- dufoq3
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
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
V-yubandi-msftCommunity Support
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 :
- Open the Power Query Editor (Transform Data).
- Select the PARTICIPANT_ID column.
- Navigate to Transform Group By.
- In the Group By window.
- For the new column name, leave it as All Data.
- Set the operation to All Rows (not Count).
- 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!
