Help With Data Condense

[arosenberg]

Hi All,

I am looking to do a bit of data cleaning but am having trouble with one thing. I have a few clients and employees with information in separate lines, please see below for a sample.

		Employee ID 1	Employee ID 2	Employee ID 3	Employee ID 4	Employee ID 5	Employee ID 6	Employee ID 7
		Employee Name 1	Employee Name 2	Employee Name 3	Employee Name 4	Employee Name 5	Employee Name 6	Employee Name 7
Client # A	Client A	0.5
Client # A	Client A		0.5
Client # A	Client A			1
Client # B	Client B				0.25
Client # B	Client B							0.5
Client # B	Client B						0.25
Client # C	Client C					1

I am trying to combine this data for each client into one row, similar to the below.

		Employee ID 1	Employee ID 2	Employee ID 3	Employee ID 4	Employee ID 5	Employee ID 6	Employee ID 7
		Employee Name 1	Employee Name 2	Employee Name 3	Employee Name 4	Employee Name 5	Employee Name 6	Employee Name 7
Client # A	Client A	0.5	0.5	1
Client # B	Client B				0.25		0.25	0.5
Client # C	Client C					1

Any help would be much appreciated. I have struggled with this in Power Query but I am a novice with Power.

Thank you in advance for the help.

Andrew

[Irwan · Accepted solution]

hello @arosenberg 

you can do this with PQ or DAX depend on what you need.

PQ : 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUVBWcFTSgXFATAMdUyCpgBXH6uDWp4BHLyF9IGyIX5cTQpcTmkoDHSNTsjSiGmJKsj64xSganREanbFoQvgzNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, #"Employee Name 1" = _t, #"Employee Name 2" = _t, #"Employee Name 3" = _t, #"Employee Name 4" = _t, #"Employee Name 5" = _t, #"Employee Name 6" = _t, #"Employee Name 7" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Employee Name 1", type number}, {"Employee Name 2", type number}, {"Employee Name 3", type number}, {"Employee Name 4", type number}, {"Employee Name 5", type number}, {"Employee Name 6", type number}, {"Employee Name 7", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1", "Column2"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
#"Pivoted Column"

what you do in PQ is basically you unpivot then pivot back. it will automatically remove null value.

DAX: 

Summarize =
SUMMARIZECOLUMNS(
    'Table'[Column1],
    'Table'[Column2],
    "Employee 1",MAX('Table'[Employee Name 1]),
    "Employee 2",MAX('Table'[Employee Name 2]),
    "Employee 3",MAX('Table'[Employee Name 3]),
    "Employee 4",MAX('Table'[Employee Name 4]),
    "Employee 5",MAX('Table'[Employee Name 5]),
    "Employee 6",MAX('Table'[Employee Name 6]),
    "Employee 7",MAX('Table'[Employee Name 7])
)

in DAX, you need to create a new table to summarize original table with looking for value on each column.

Hope this will help.

Thank you.