Convert a single row w/ employee and dependents row into individual rows for each person

New Contributor

Looking for some help in converting a data table. I'm thinking PowerQuery may be the solution.  

 

I need to convert the below tablet to where each person, employee and dependent, has their own row. Currently, I have the employee occupying each row where they have dependents. If you go to the right (actually what would be column U. ) you see the dependent ID, employee_client_value_8. Ideally, each person would have their own row, share the common employee ID, but maintain their own ID. Their names would also be in a new field, Name.

 

Is this a job for PowerQuery?

 

 

employee_client_idemployee_nameemployee_dobemployee_street_1employee_street_2employee_cityemployee_stateemployee_zipemployee_phoneemployee_emailemployee_client_value_1employee_client_value_2employee_client_value_3employee_client_value_4employee_client_value_5employee_client_value_6employee_client_value_7employee_client_value_8employee_client_value_9employee_client_value_10dependent_client_iddependent_namedependent_reldependent_dobdependent_productsdependent_client_value_1dependent_client_value_2dependent_client_value_3dependent_client_value_4dependent_client_value_5dependent_client_value_6dependent_client_value_7dependent_client_value_8dependent_client_value_9dependent_client_value_10
C0126John Smith312442612 W Plaza AveZenithKY40255502-123-555Email address removed        C0126-AMegan SmithSP30941MDV             
C0126John Smith312442612 W Plaza AveZenithKY40255502-123-555Email address removed        C0126-BOlivia SmithCH40846MDV             
C0126John Smith312442612 W Plaza AveZenithKY40255502-123-555Email address removed        C0126-CDaniel SmithCH41306MDV             
C0127Tanya Doe23143323 Robin BlvdChester's MillOH421228112-999-5512Email address removed        C0127-AJuan DoeSP30941DV             
C0127Tanya Doe23143323 Robin BlvdChester's MillOH421228112-999-5512Email address removed        C0127-BIzaiah DoeCH40846MDV             
C0127Tanya Doe23143323 Robin BlvdChester's MillOH421228112-999-5512Email address removed        C0127-CXavier DoeCH41306MDV             
C0127Tanya Doe23143323 Robin BlvdChester's MillOH421228112-999-5512Email address removed        C0127-DCara DoeSC36495D             
1 Reply

@sbisa Yes! Definitely something for PQ. Can you show, based on the example data you provided how exactly the end result should look like? That is, how you would cut and past it manually to get the table you want.

 

Edit: Had a closer look at the data and thought that it could look like in the attached file.