Multiple rows into one row

[inglexjc]

I have a table that has case ID  (same id showing up in multiple rows) listing what program they receive in different rows.  I'm wanting to be able to show that a case is receiving multiple programs and what those programs are. I think the easiest way would to combine the rows for each case into one row instead of multiple.

Example below of how it's coming in:

CS_ID	HIGH_LEVEL_PGM	LOW_LEVEL_PGM	CS_STS_CD	PGM_STS_CD
1	SNAP	SNAP	OP	AP
1	Medical Assistance	MAGI Adult	OP	AP
2	Medical Assistance	SSI Mandatory	OP	AP
3	Medical Assistance	SSI Mandatory	OP	AP
2	Medical Assistance	QMB	OP	AP
3	SNAP	SNAP	OP	AP
2	Adult Financial	OAP A	OP	AP
4	Adult Financial	OAP A	OP	AP
3	Adult Financial	OAP A	OP	AP
5	Medical Assistance	OAP Med-A	OP	AP
5	Adult Financial	OAP A	OP	AP
5	SNAP	SNAP	OP	AP
6	Medical Assistance	OAP Med-A	OP	AP
7	Medical Assistance	SSI Mandatory	OP	AP
8	Medical Assistance	QMB	OP	AP
9	Medical Assistance	QMB	OP	AP
8	Medical Assistance	SSI Mandatory	OP	AP
8	SNAP	SNAP	OP	AP
7	Medical Assistance	QMB	OP	AP
7	SNAP	SNAP	OP	AP

How I'd like it:

CS_ID	HIGH_LEVEL_PGM	LOW_LEVEL_PGM	CS_STS_CD	PGM_STS_CD
1	SNAP	MAGI Adult	OP	AP
2	SSI Mandatory	QMB	OP	AP
3	SNAP	OAP	OP	AP

[inglexjc]

Case ID represents a client.  I need to show how many programs the client is receiving.  Since the each program creates a new row it makes it difficult to show in one row what programs the client receives.

I want all the info for the client to show in one row, instead of multiple rows.

[Anonymous]

Hi @inglexjc ,

You can paste my code as below into advanced editor to learn more details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQr2cwxAUP4gAsiK1YHI+qamZCYn5ig4FhdnFpck5iWnggQd3T0VHFNKc0rQdBjh0hEc7Kngm5iXkliSX1SJpsmYHE04bQr0dcJiPm5PggwCe0XBLTMPaEZmYg5IhWOAgiOaShOiVRoTrdIUlzdAioESutg0EG80bm+bkWqxOTmxZEF8LFkSrxSnqYScgjs4cPoO03Zz3AbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CS_ID = _t, HIGH_LEVEL_PGM = _t, LOW_LEVEL_PGM = _t, CS_STS_CD = _t, PGM_STS_CD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CS_ID", Int64.Type}, {"HIGH_LEVEL_PGM", type text}, {"LOW_LEVEL_PGM", type text}, {"CS_STS_CD", type text}, {"PGM_STS_CD", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Combine HIGH_LEVEL_PGM", each Text.Combine(
let _id = [CS_ID]
in
Table.SelectRows(#"Changed Type",each [CS_ID] = _id)[HIGH_LEVEL_PGM]," / ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Combine LOW_LEVEL_PGM", each Text.Combine(
let _id = [CS_ID]
in
Table.SelectRows(#"Changed Type",each [CS_ID] = _id)[LOW_LEVEL_PGM]," / ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"HIGH_LEVEL_PGM", "LOW_LEVEL_PGM"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[inglexjc]

That did give me: 

How ever it changed my cs_id numbers and narrowed my data down and isn't accurate.

This is just a small snip it of the data.  There are over 2,000 cases.

Notice how cases can have more than one program associated with it, that's why it's listed more than once.  I'd like it all to show on one line so I can show what Longer Term Care case has other programs associated with it.

[latimeria]

Hi @inglexjc ,

Not clear what you want.
id 1 : high from first row? low from last row?
id 2: SSI mandatory : where does it come from?