Good evening
Please can you assist. I have a training dataset which I need to be reformatted so that I can use pivot tables to identity what courses are outstanding and who has completed the online training. (1 = completed & 0 = outstanding)
I have tried to format the data into columns using unpivot and split columns but was unable to go any further because the data that is in one column is all joined together.
For an example, I copied from the original CSV file the column N header title in the attachment:
G4：Introduction
G40003 Work safety [Related to electricity_ fire]：Safety
What I need assistance is to format all the headers e.g.
the “Introduction” and “Course Code” with “Description” as follows:
Subject “Introduction” (in one column)
The course codes: “G40003” with Subject “Work safety [Related to Electricity_fire]” (into one column)
I am open to suggestions.
I have attached the data set with personal information removed.
Thank you for your help.
Since you asked for a table that you can use to create your own pivot tables from, the following PQ script will do just that. Point the Source step to the CSV on your system. Everything is done by clicking in the User Interface. At the end I cleaned-up a bit by renaming the steps to words without spaces. Looks neater that way.
let
Source = Csv.Document(File.Contents("C:\Users\......\TRAINING DATA.csv"),[Delimiter=",", Columns=144, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
Unpivot = Table.UnpivotOtherColumns(PromoteHeaders, {"Name"}, "Attribute", "Value"),
Split1 = Table.SplitColumn(Unpivot, "Attribute", Splitter.SplitTextByEachDelimiter({"#(lf)"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
Split2 = Table.SplitColumn(Split1, "Attribute.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.2.1", "Attribute.2.2"}),
Extract1 = Table.TransformColumns(Split2, {{"Attribute.2.2", each Text.BeforeDelimiter(_, "："), type text}}),
Extract2 = Table.TransformColumns(Extract1, {{"Attribute.1", each Text.AfterDelimiter(_, "："), type text}}),
Rename = Table.RenameColumns(Extract2,{{"Attribute.2.1", "CourseCode"}, {"Attribute.2.2", "Description"}, {"Value", "Status"}, {"Attribute.1", "Subject"}}),
ChangeType = Table.TransformColumnTypes(Rename,{{"Status", Int64.Type}})
in
ChangeType
@Riny, thanks very much for your help. It is exactly what I wanted. In hindsight it looks simple to do.
Thank you
I tried to include the personal data columns A:K into power query but they did not under refresh. I suspect that the code needs to be changed. I assumed that it would automatically pull through.
I have attached the master file which now covers all the Target Grades G1-4. A total of 471 columns. (The one you did for me was only for the G4 grade) In the Target Grade column K, I don't need four vertical lines.
NB: the attached file is downloaded from a LMS platform.
I appreciate your help.
The script I gave you earlier selects "Name" and unpivots all other columns. If want the first 11 columns to stay in place and all others unpivoted, remove the original unpivot step, select those 11 columns (note that there is no such thing as columns A:K in PQ) and then right-click and "Unpivot other......".
The unpivot step then looks like this:
Unpivot = Table.UnpivotOtherColumns(PromoteHeaders, {"User ID", "Employee ID", "Name", "Distributor", "DLR ID", "DLR/WS Code", "DLR Name", "WS Name", "Current Grade", "Date of current certification", "Target Grade"}, "Attribute", "Value"),
You'll create a lot of redundancy, if that's really what you want. Otherwise consider creating two tables. One with only the Employee ID and all the training data. Remove all columns with employee details. The use the following Unpivot step:
Unpivot = Table.UnpivotOtherColumns(PromoteHeaders, {"Employee ID"}, "Attribute", "Value"),
And another with the all employee data where the EmployeeID is the key field. So remove all the course detail columns. Then you could use the Data model (Power Pivot) to build pivot tables based on any of the Employee data elements by creating a relationship between the two tables based on the EmployeeID. Just an idea.