Power Query Data Stacking/Unstacking Inquiry

[Max_Petti]

Hello,

I have a table which is formatted as followed:

Account #

Owner 1 Name

Owner 1 SSN

Owner 1 DOB

Owner 1 Email

Owner 2 Name

Owner 2 SSN

Owner 2 DOB

Owner 2 Email

Owner 3 Name

Owner 3 SSN

Owner 3 DOB

Owner 3 Email

1

John Smith

123123123

12/12/1950

email address removed for privacy reasons

Sally Smith

321321321

12/12/1960

email address removed for privacy reasons

Adam Smith

111222333

12/12/1970

email address removed for privacy reasons

2

John Smith

123123123

12/12/1950

email address removed for privacy reasons

3

John Smith

123123123

12/12/1950

email address removed for privacy reasons

Adam Smith

111222333

12/12/1970

email address removed for privacy reasons

each row represents an account in which 1, or more owners may be listed.  These owneres may also be listed on additional rows, and may also appear in differing sections (i.e owner 1, 2, 3 sets of headers to accomodate data gathering)

My goal is to use power query to transform the table to the following format:

Account #	Owner Name	Owner SSN	Owner DOB	Owner Email
1	John Smith	123123123	12/12/1950	email address removed for privacy reasons
1	Sally Smith	321321321	12/12/1960	email address removed for privacy reasons
1	Adam Smith	111222333	12/12/1970	email address removed for privacy reasons
2	John Smith	123123123	12/12/1950	email address removed for privacy reasons
3	John Smith	123123123	12/12/1950	email address removed for privacy reasons
3	Adam Smith	111222333	12/12/1970	email address removed for privacy reasons

I want to create a continguous/stacked data set for all account owners across multiple sets of columns, to just one set of columns to capture owner data.

Please let me know if anything is unclear, or if additional clarification is needed!

Thank You,

[#2 · Accepted solution]

@Max_Petti 

You can try this Power Query suggestion. In the attached file you can add data to the large blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

The data layout in the screenshot and in the attached file is for illustration. You can place the green result table to the right of the blue table or in another worksheet.

[#3]

@OliverScheurich Thank you, this looks great.

Would it be possible by chance to list/provide a screenshot of your applied steps for transformation?  I unfortunately am not able to open the file at this time.

Disregard - I was able to open the file.  Another question, would the steps work if i were to enter partial records? i.e. missing a DOB, or email?

[#4]

@Max_Petti 

You are welcome. The solution doesn't work for partial records. It requires all the information which is expected in the output table (name, ssn, dob and email).