cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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

sbisa
Copper Contributor

‎Jan 14 2022 06:09 PM - edited ‎Jan 14 2022 06:15 PM

‎Jan 14 2022 06:09 PM - edited ‎Jan 14 2022 06:15 PM

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

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             
761 Views
0 Likes
1 Reply
Reply
1 Reply
Riny_van_Eekelen

‎Jan 14 2022 10:07 PM - edited ‎Jan 15 2022 12:43 AM

‎Jan 14 2022 10:07 PM - edited ‎Jan 15 2022 12:43 AM

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

@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.

 

Preview file
23 KB
0 Likes
Reply