Unstack, Flatten or Compress data into a single row per employee

[Bill_Kro]

I have a download from by HR system but I'm having problems getting the data into a format for the report I have to generate.

I want a report that looks like the following...

Employee Name	Medical	Dental	Vision	Vision Pay	Flex
Emp1	MedBasicFamily	DenBasicFam	Vision	$18.00	Flex
Emp2	MedBasicSingle		Vision	$8.50

<tbody>
</tbody>

The data that I have looks like this...

<tbody></tbody>

Employee Name Medical Dental Vision Vision Pay Flex Emp1 DenBasicFam Emp1 MedBasicFamily Emp1 Vision $18.00 Emp1 Flex Emp2 MedBasicSingle Emp2 Vision $8.50 ... <tbody> </tbody>

<colgroup><col width="209"><col width="198" span="3"><col width="90"><col width="198"></colgroup><tbody>
</tbody>

What is the easiest way to compress this data to one line per employee?

 

[sandy666]

is that what you want?

done with PowerQuery (Get&Transform)

Employee Name

Medical

Dental

Vision

Vision Pay

Flex

Employee Name

Medical

Dental

Vision

Vision Pay

Flex

Emp1

DenBasicFam

Emp1

MedBasicFamily

DenBasicFam

Vision

$18.00

Flex

Emp1

MedBasicFamily

Emp2

MedBasicSingle

Vision

$8.50

Emp1

Vision

$18.00

Emp1

Flex

Emp2

MedBasicSingle

Emp2

Vision

$8.50

 

[Bill_Kro]

Yes, this is what I want to do.

 

[sandy666]

here is a M-code for example above

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Medical", type text}, {"Dental", type text}, {"Vision", type text}, {"Vision Pay", type text}, {"Flex", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Medical", each List.Distinct(Table.Column([Count],"Medical"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Medical", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Dental", each List.Distinct(Table.Column([Count],"Dental"))),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Dental", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values1", "Vision", each List.Distinct(Table.Column([Count],"Vision"))),
    #"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"Vision", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom3" = Table.AddColumn(#"Extracted Values2", "Vision Pay", each List.Distinct(Table.Column([Count],"Vision Pay"))),
    #"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Vision Pay", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom4" = Table.AddColumn(#"Extracted Values3", "Flex", each List.Distinct(Table.Column([Count],"Flex"))),
    #"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"Flex", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values4"[/SIZE]