Forum Discussion
Help With Data Condense
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
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.
1 Reply
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.