How to normalize flat data

[CMSGuy]

Good morning!  I have a table that contains flat data.  The table is a record of an employee and a certification they are working on.  Each day a daily record is entered indicating the employee, certification, and task.  Each certification can have a number of different tasks.  The flat table has up to 32 tasks (task names, tasks instances needed, task instances submitted).  I need to normalize that data for a matrix.  

This is a sample of the flat data:

Emp Full NameCertification IDCertification TitleTask 1Task 1 Instances SubmittedTask 1 Instances NeededTask 2Task 2 Instances SubmittedTask 2 Instances NeededTask 3Task 3 Instances SubmittedTask 3 Instances Needed

Michael D. Arksey	3	LG-8 GAS LINES CONSTRUCTION EQUIPMENT OPERATOR	Meter Turn On & Light Ups	0	15	Meter Exchanges	0	15	Meter Stand Relocate	0	25
Christian J. Spencer	3	LG-8 GAS LINES CONSTRUCTION EQUIPMENT OPERATOR	Meter Turn On & Light Ups	0	15	Meter Exchanges	0	15	Meter Stand Relocate	0	25
Brad K. Bagozzi Jr	3	LG-8 GAS LINES CONSTRUCTION EQUIPMENT OPERATOR	Meter Turn On & Light Ups	0	15	Meter Exchanges	0	15	Meter Stand Relocate	0	25
Devin A. King	3	LG-8 GAS LINES CONSTRUCTION EQUIPMENT OPERATOR	Meter Turn On & Light Ups	0	15	Meter Exchanges	0	15	Meter Stand Relocate	0	25
Joshua T. Diehl	3	LG-8 GAS LINES CONSTRUCTION EQUIPMENT OPERATOR	Meter Turn On & Light Ups	0	15	Meter Exchanges	0	15	Meter Stand Relocate	0	25
Blayne J. Harper	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
James G. Delauter	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	1	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
Blayne J. Harper	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	1	60
Eric A. Roman	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	1	60	Service Redline	7	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
Blayne J. Harper	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	1	60
Blayne J. Harper	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	1	60
Tony J. Jones	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
Tony J. Jones	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
Tony J. Jones	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
John G. Smith Jr	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
Aaron S. Allison	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
James G. Delauter	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	2	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
James G. Delauter	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
John G. Smith Jr	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	1	60
John G. Smith Jr	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
John G. Smith Jr	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	0	60
John G. Smith Jr	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	6	60	Service Redline	11	30	CLIC (Plastic MAIN CREW 1-4 employees)	1	60
Eric A. Roman	2	LG-12 GAS LINES CONSTRUCTION WORKER	CLIC (Plastic SERVICE CREW (1-4 employees)	0	60	Service Redline	0	30	CLIC (Plastic MAIN CREW 1-4 employees)	1	60

Now what is ultimately needed is a normalized table to show:

• Employee
• Certification ID
• Task Number
• Task ID (or name)
• Instances Needed
• Instances Submitted

I am pretty sure that somehow the data needs a pivot, but unsure as to how to do it.  Thanks for any help or direction

[Teskevm]

do you mean like this?

This will take some more manual work then the other option but is more normalized!
You can probably automate it as well but that will take some more research 🙂

[CMSGuy]

Yes, that is what I need.  How did you do it?

[Greg_Deckler]

@CMSGuy The code I posted does this. Just paste it into a blank query using Advanced Editor and you can step through it.

[CMSGuy]

Thanks Greg!  It does work and I assume then I would just change the source to the real source, which is a SharePoint list.  And then I need to work in the remaining 30 tasks 🙂 

[Greg_Deckler · Accepted solution]

@CMSGuy Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZZNU4MwEIb/yg4nnVEGUKtXSjMV2kIlVA/VQ4bulIxp0gnUsf56Q+vHQXtyqqAewiXL5nmzu9mdTq0RzwuGAno2+Pq+xLV1ZJ2YNewfX0DfpzAMY0IhSGKapZMgC5MYyNUkHI9InEEyJqmfJan5YYQVashWWkIi4XblOF4HhnxeVDBZlsbAMcs9e7Mkj+ZgOcdPtmjF5AxSFCpnFb7se2fW3dHUCgrNy4ozCZENdIkyR91w5K5mMxjY0GVz9fTEIWo6cA8fuATfhgGX84azRqosVgwyG3ocC9Fw2q5ga4l16l4yvdxkrrfFdb1dvDdJOiA1YzAMAzgYC2byPwdK0uswIBCk5AYO3ONTwMVSqDViefhyaKf+UNQPPEcDNBNcvvKcOB8cjvww3nrb5Wxz32yBJfTNdaNgq2qfEtw9SWhpENx3BUQbU1OfqVow+SMBOP/bAWi/gkzJdc0fKbl5xNqA7/zjNwM/UoWsewBd8KrYzjMtU+AzrSRQM/QKwUu1x0f0FzRir/0S/ithVyNoqYL2x+BHFHR2K3Ddr6TRN42ke6mCu2c=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp Full Name" = _t, #"Certification ID" = _t, #"Certification Title" = _t, #"Task 1" = _t, #"Task 1 Instances Submitted" = _t, #"Task 1 Instances Needed" = _t, #"Task 2" = _t, #"Task 2 Instances Submitted" = _t, #"Task 2 Instances Needed" = _t, #"Task 3" = _t, #"Task 3 Instances Submitted" = _t, #"Task 3 Instances Needed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp Full Name", type text}, {"Certification ID", Int64.Type}, {"Certification Title", type text}, {"Task 1", type text}, {"Task 1 Instances Submitted", Int64.Type}, {"Task 1 Instances Needed", Int64.Type}, {"Task 2", type text}, {"Task 2 Instances Submitted", Int64.Type}, {"Task 2 Instances Needed", Int64.Type}, {"Task 3", type text}, {"Task 3 Instances Submitted", Int64.Type}, {"Task 3 Instances Needed", Int64.Type}}),
    Task1 = 
            Table.SelectColumns(#"Changed Type",{"Emp Full Name", "Certification ID", "Certification Title", "Task 1", "Task 1 Instances Submitted", "Task 1 Instances Needed"}),
    Task2 = Table.SelectColumns(#"Changed Type",{"Emp Full Name", "Certification ID", "Certification Title", "Task 2", "Task 2 Instances Submitted", "Task 2 Instances Needed"}),
    Task3 = 
        Table.SelectColumns(#"Changed Type",{"Emp Full Name", "Certification ID", "Certification Title", "Task 3", "Task 3 Instances Submitted", "Task 3 Instances Needed"}),
    #"Append" = 
        Table.Combine({Task1, Task2, Task3})
in
    #"Append"