Transpose Data in Columns to Rows

[DFNJ]

I'm trying to move data from certains columns to rows. I've tried unpivot, but it doesn't seem to keep certain data points together. This is my PQ.
View attachment 103352

Here's the new format I need to create. Can you please advise? Thank you!

 

[alansidman]

cannot manipulate data in a picture. Suggest you reload using XL2BB and then show us also, what you wish the end result to look like. If that was in your attachment, then it was unreadable.

 

[DFNJ]

cannot manipulate data in a picture. Suggest you reload using XL2BB and then show us also, what you wish the end result to look like. If that was in your attachment, then it was unreadable.

I downloaded XL2BB. I thought I had uploaded the query table in the attachment. The image is the new format that it needs to be in. I'll try to reload the image in XL2BB.

SSN	First Name	Last Name	Birthdate	Gender	Relationship Code	Address 1	Address 2	City	State	Zip	SPOUSE First Name	SPOUSE Last Name	SPOUSE Birthdate	SPOUSE Gender	SPOUSE Relationship	DEP1. First Name	DEP1. Last Name	DEP1. Birthdate	DEP1. Gender	DEP1. Relationship	DEP2. First Name	DEP2. Last Name	DEP2. Birthdate	DEP2. Gender	DEP2. Relationship	DEP3. First Name	DEP3. Last Name	DEP3. Birthdate	DEP3. Gender	DEP3. Relationship	Plan	Election Effective date
111-11-1111	MARIA	BROWN	9/28/67	F	SUB	1 MAIN ST	Unit 103	Chicago	IL	60614						KELLY	BROWN	7/28/05	F	DEP	Matthew	BROWN	9/17/07	M	DEP						HIGH PLAN	1/1/24
222-22-2222	LISA	SMITH	7/23/66	F	SUB	2 TREE LN		FALLS CHURCH	VA	22046	James	SMITH	12/24/62	M	SPS																HIGH PLAN	1/1/24
333-33-3333	ANN	FORD	6/20/81	F	SUB	3 BROOK DR	APT 1	SKOKIE	IL	60077	ANDREW	FORD	12/14/76	M	SPS	JAKE	DAVIS	2/7/06	M	DEP	John	WHITE	2/3/12	M	DEP	Peter	WHITE	4/13/13	M	DEP	LOW PLAN	1/1/24

 

[DFNJ]

This is the new format I'm trying to achieve.

Reformat_New.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Reformat
2	SUB SSN	First Name	Last Name	Birthdate	Gender	Relationship Code	Address 1	Address 2	City	State	Zip	Plan	Election Effective date
3	361-70-1988	MARIA	BROWN	9/28/67	F	SUB	1 MAIN ST	Unit 103	Chicago	IL	60614	HIGH PLAN	1/1/24
4	361-70-1988	KELLY	BROWN	7/28/05	F	DEP	1 MAIN ST	Unit 103	Chicago	IL	60614	HIGH PLAN	1/1/24
5	361-70-1988	Matthew	BROWN	9/17/07	M	DEP	1 MAIN ST	Unit 103	Chicago	IL	60614	HIGH PLAN	1/1/24
6	213-92-7188	LISA	SMITH	7/23/66	F	SUB	2 TREE LN		FALLS CHURCH	VA	22046	HIGH PLAN	1/1/24
7	213-92-7188	James	SMITH	12/24/62	M	SPS	2 TREE LN		FALLS CHURCH	VA	22046	HIGH PLAN	1/1/24
8	319-08-9364	ANN	FORD	6/20/81	F	SUB	3 BROOK DR	APT 1	SKOKIE	IL	60077	LOW PLAN	1/1/24
9	319-08-9364	ANDREW	FORD	12/14/76	M	SPS	3 BROOK DR	APT 1	SKOKIE	IL	60077	LOW PLAN	1/1/24
10	319-08-9364	JAKE	DAVIS	2/7/06	M	DEP	3 BROOK DR	APT 1	SKOKIE	IL	60077	LOW PLAN	1/1/24
11	319-08-9364	John	WHITE	2/3/12	M	DEP	3 BROOK DR	APT 1	SKOKIE	IL	60077	LOW PLAN	1/1/24
12	319-08-9364	Peter	WHITE	4/13/13	M	DEP	3 BROOK DR	APT 1	SKOKIE	IL	60077	LOW PLAN	1/1/24
Enr

 

[JGordon11]

try

let
    tbl = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.RenameColumns(tbl, {"Relationship Code", "Relationship"}),
    tcn = Table.ColumnNames(Source),
    headers = List.Range(tcn, 1, 5),
    rel = {"", "SPOUSE ", "DEP1. ", "DEP2. ", "DEP3. "},
    commoncols =  List.Range(tcn, 6,5) & List.LastN(tcn,2), 
    lstheaders = List.Accumulate(rel, {}, (s,c)=> s & {{"SSN"} & List.Transform(headers, each c & _) & commoncols}),
    lsttables = List.Transform(lstheaders, each Table.SelectColumns(Source, _)),
    tcn1 = Table.ColumnNames(lsttables{0}),
    lsttables1 = List.Transform(lsttables, each Table.RenameColumns(_, List.Zip({Table.ColumnNames(_), tcn1}))),
    listtables2 = List.Zip({lsttables1, {1..List.Count(lsttables1)}}),
    listtables3 = List.Transform(listtables2, each Table.AddColumn(_{0}, "Order", (x)=> _{1})),
    tbl1 = Table.Combine(listtables3),
    tbl2 = Table.SelectRows(tbl1, each ([Last Name] <> null)),
    tbl3 = Table.Sort(tbl2,{{"SSN", Order.Ascending}, {"Order", Order.Ascending}}),
    Result = Table.RenameColumns(Table.RemoveColumns(tbl3,{"Order"}),{"Relationship", "Relationship Code"})
in
    Result

 

[DFNJ]

try

let
    tbl = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.RenameColumns(tbl, {"Relationship Code", "Relationship"}),
    tcn = Table.ColumnNames(Source),
    headers = List.Range(tcn, 1, 5),
    rel = {"", "SPOUSE ", "DEP1. ", "DEP2. ", "DEP3. "},
    commoncols =  List.Range(tcn, 6,5) & List.LastN(tcn,2),
    lstheaders = List.Accumulate(rel, {}, (s,c)=> s & {{"SSN"} & List.Transform(headers, each c & _) & commoncols}),
    lsttables = List.Transform(lstheaders, each Table.SelectColumns(Source, _)),
    tcn1 = Table.ColumnNames(lsttables{0}),
    lsttables1 = List.Transform(lsttables, each Table.RenameColumns(_, List.Zip({Table.ColumnNames(_), tcn1}))),
    listtables2 = List.Zip({lsttables1, {1..List.Count(lsttables1)}}),
    listtables3 = List.Transform(listtables2, each Table.AddColumn(_{0}, "Order", (x)=> _{1})),
    tbl1 = Table.Combine(listtables3),
    tbl2 = Table.SelectRows(tbl1, each ([Last Name] <> null)),
    tbl3 = Table.Sort(tbl2,{{"SSN", Order.Ascending}, {"Order", Order.Ascending}}),
    Result = Table.RenameColumns(Table.RemoveColumns(tbl3,{"Order"}),{"Relationship", "Relationship Code"})
in
    Result

Thank you. I copied the code as a new step, but I received an error.

try

let
    tbl = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.RenameColumns(tbl, {"Relationship Code", "Relationship"}),
    tcn = Table.ColumnNames(Source),
    headers = List.Range(tcn, 1, 5),
    rel = {"", "SPOUSE ", "DEP1. ", "DEP2. ", "DEP3. "},
    commoncols =  List.Range(tcn, 6,5) & List.LastN(tcn,2),
    lstheaders = List.Accumulate(rel, {}, (s,c)=> s & {{"SSN"} & List.Transform(headers, each c & _) & commoncols}),
    lsttables = List.Transform(lstheaders, each Table.SelectColumns(Source, _)),
    tcn1 = Table.ColumnNames(lsttables{0}),
    lsttables1 = List.Transform(lsttables, each Table.RenameColumns(_, List.Zip({Table.ColumnNames(_), tcn1}))),
    listtables2 = List.Zip({lsttables1, {1..List.Count(lsttables1)}}),
    listtables3 = List.Transform(listtables2, each Table.AddColumn(_{0}, "Order", (x)=> _{1})),
    tbl1 = Table.Combine(listtables3),
    tbl2 = Table.SelectRows(tbl1, each ([Last Name] <> null)),
    tbl3 = Table.Sort(tbl2,{{"SSN", Order.Ascending}, {"Order", Order.Ascending}}),
    Result = Table.RenameColumns(Table.RemoveColumns(tbl3,{"Order"}),{"Relationship", "Relationship Code"})
in
    Result

Thank you. I received this error message. Do I need to rename "Table1?" Where do I find this?

 

[JGordon11]

Create a blank query, go to the advanced editor, delete the blank query code, and paste the code I wrote.

The first step in the query is to retrieve the starting table. So if your starting table is in excel and has the name Table1, then it will be retrieved in the first step. If your table is named something else then change "Table1" in the first step to your table name.