Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to 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"
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 🙂
Yes, that is what I need. How did you do it?
@CMSGuy The code I posted does this. Just paste it into a blank query using Advanced Editor and you can step through it.
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 🙂
@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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.