Transposing data vertically to horizontally for duplicate vs non duplicate data

[lizsunnysideup]

Hello there. I was given a task that has 40k rows of member data and drugs. Column D has member ID numbers but the report includes duplicate member who have filled different medications so the report looks like this:

Column D Column O
0001 VIAGRA 50MG
0001 LISINOPRIL 10MG
0001 VIAGRA 50MG
0001 SEVERVENT INHALER
0002 FINASTERIDE 5MG
0003 ELIQUIS 10MG
0003 LISINIOPRIL 20MG

How do I remove the duplicates from Column D and O AND how do I go from listing each individual drug for people that filled more than 1 drug horizontally in Column P, Q, R, etc with each drug moving to the next column to the right? So for instance:

Column D Column O Column P Column Q
0001 VIAGRA 50MG LISINORPIL 10MG SEREVENT INHALER
0002 FINASTERIDE 5MG
0003 ELIQUIS 10MG LISINOPRIL 20MG

 

[alansidman]

I would use Power Query to get the expected results

Book6
	A	B	C	D	E	F	G	H
2	1	VIAGRA 50MG		1	VIAGRA 50MG	LISINOPRIL 10MG	VIAGRA 50MG	SEVERVENT INHALER
3	1	LISINOPRIL 10MG		2	FINASTERIDE 5MG
4	1	VIAGRA 50MG		3	ELIQUIS 10MG	LISINIOPRIL 20MG
5	1	SEVERVENT INHALER
6	2	FINASTERIDE 5MG
7	3	ELIQUIS 10MG
8	3	LISINIOPRIL 20MG
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Count", each _, type table [Column1=number, Column2=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Index"}, {"Column1", "Column2", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US")[Index]), "Index", "Column2")
in
    #"Pivoted Column"

 

[lizsunnysideup]

That worked! Thank you so much!