Power Query: Splitting Table & Advance Grouping

[WARJAMAND]

Hi - 

 I have a table that has multiple rows for every employees associated with individual ID Numbers. Each Employee is enrolled in a number of benefits (Medical, Dental, Vision, etc.). Each benefit have a:

- Benefit Effective Date

- Benefit Name

- Benefit Coverage Level

- Benefit Employee Contribution (or Cost)

- Benefit Employer Contribution (or Cost)

I performed an Advance Grouping By transformation to the table so that employees now only have one (1) row, I wish to show each of the possible ten (10) different: Benefits Effective Date; Benefits Plan Name, Benefit Coverage Level; Benefit Employee Contribution , and Benefit Employer Contribution. 

The Split will need to be dynamic to ensure if future benefits plans are added, it will not break any M formulas (hence, I included in the Advance Grouping: NQuals with the Operation to Count All Rows). There is a Maximum of 10 rows within the table (range 1 - 10).

In Summary, I only what each employee to have one row with the name with column names of the different benefits that they are enrolled in.  Not all employees are enrolled in all 10 benefits.

I have attached I file that I believe may help visualize what is needed, There is a Query included within the file that I started.  Thank-you for your help~

[#2]

@WARJAMAND Not convinced I did it the most effective way, but the attached workbook "merges" all rows per ID into a single row, by grouping and adding a series of columns, before expanding and cleaning up a bit. See if this method works on your real data.

[#3]

@Riny_van_Eekelen 

Hi Riny_van_Eekelen

Thank-you very much. I was able to come to the same conclusion that you provided in an earlier Power Query table. However I would like for the results (benefits ) to populate the column that is already created within the sample file I attached . Example Clark Kent is enrolled in Medical - CIGNA HMO... the column titled "Medical" should show Medical - CIGNA HMO for Clark Kent's Row.

Per the new file I attached, I am able to label and add Conditional Columns by each benefit (for time purposes I only listed Medical, Dental, Vision and Voluntary Hospital Care benefits and filtered out all others).  Once this is done I could fill the conditional columns either "Up" or "Down", however this method does not work if there are numerous  EE's who did not elect a particular benefit (Please review Voluntary Hospital Care - Rows 38 thru 57). 

So in summary , I am wondering if there is a Table function within Power Query that could help populate unique rows with all the information described above. 

Thank-you for all your help!

Warren