Code Documentation for Power Query M Script

This document provides a detailed overview of the Power Query M script that processes a CSV file containing breakage reports and transforms the data into a more structured format.

Overview

The script performs the following key operations:

1. Loads data from a CSV file.
2. Promotes the first row to headers.
3. Changes the data types of specified columns.
4. Cleans up and renames columns.
5. Joins with additional tables to enrich data fields.
6. Removes unnecessary columns and prepares the final data table.

---

Code Breakdown

1. Load CSV Data

let
    Source = Csv.Document(File.Contents("\\sv-999-fs01\EyenceShares\Reports\s_breakagekm.csv"),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),

• Purpose: Load data from a specified CSV file.
• Parameters:
  • File.Contents: Reads the content of the file from the given path.
  • Delimiter: Specifies the character used to separate values (,).
  • Columns: Number of columns expected in the file.
  • Encoding: Specifies the character encoding (1252 is for Windows-1252).
  • QuoteStyle: Defines how to handle quotes in the CSV (none in this case).

2. Promote Headers

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

• Purpose: Promotes the first row of the data as the header row.
• Parameters:
  • PromoteAllScalars: Promotes all scalar values.

3. Change Column Types

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Breakage Code", type text}, {"Employee Code", type text}, {"Cost", Int64.Type}, {"Quantity", Int64.Type}, {"Lens Style", type text}, {"Material", type text}, {"Date", type date}, {"Time", type time}, {"Order #", Int64.Type}, {"Breakage Description", type text}, {"Manufacturer", type text}, {"OPC", Int64.Type}, {"OPC Left", Int64.Type}, {"", type text}, {"_1", type text}, {"_2", type text}, {"_3", Int64.Type}, {"_4", Int64.Type}}),

• Purpose: Changes the data types of specified columns for accuracy and type-safety.
• Parameters:
  • type: The desired data type for each column (e.g., text, Int64.Type, date, time).

4. Rename Columns

    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Employee Code", "Temp Employee Code"}}),

• Purpose: Temporarily renames the "Employee Code" column to "Temp Employee Code".

5. Remove Unnecessary Columns

    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"", "_1", "_2", "_3", "_4"}),

• Purpose: Removes empty and unnecessary columns to clean up the dataset.

6. Clean Employee Code

    #"Uppercased Text" = Table.TransformColumns(#"Removed Columns",{{"Temp Employee Code", Text.Upper, type text}}),
    #"Only Letters & Numbers" = Table.AddColumn(#"Uppercased Text", "Employee Code", each Text.Select([Temp Employee Code],{ "A".."Z", "0".."9" })),

• Purpose:
  • Converts "Temp Employee Code" to uppercase.
  • Adds a new column "Employee Code" that retains only letters and numbers from "Temp Employee Code".

7. Reorder Columns

    #"Reordered Columns" = Table.ReorderColumns(#"Only Letters & Numbers",{"Breakage Code", "Employee Code", "Temp Employee Code", "Cost", "Quantity", "Lens Style", "Material", "Date", "Time", "Order #", "Breakage Description", "Manufacturer", "OPC", "OPC Left"}),

• Purpose: Reorders columns for better readability and organizational structure.

8. Merge with Lens Style Table

    #"Merged Queries" = Table.NestedJoin(#"Removed Columns1", {"Lens Style"}, #"tbl LensStyle", {"Code"}, "tbl LensStyle", JoinKind.LeftOuter),
    #"Expanded tbl LensStyle" = Table.ExpandTableColumn(#"Merged Queries", "tbl LensStyle", {"Code", "Lens Style"}, {"tbl LensStyle.Code", "tbl LensStyle.Lens Style"}),

• Purpose:
  • Joins with the "tbl LensStyle" table based on the "Lens Style" column using a left outer join.
  • Expands the merged columns to bring in additional data.

9. Merge with Material Description Table

    #"Merged Queries1" = Table.NestedJoin(#"Removed Columns2", {"Material"}, #"tbl MatDesc", {"Code"}, "tbl MatDesc", JoinKind.LeftOuter),
    #"Expanded tbl MatDesc" = Table.ExpandTableColumn(#"Merged Queries1", "tbl MatDesc", {"Code", "Material Description"}),

• Purpose: Similar to the Lens Style merge, this joins with the "tbl MatDesc" table to bring in material descriptions.

10. Add Department Code

    #"Added Custom" = Table.AddColumn(#"Removed Columns3", "tmpDepartment", each Text.Start([Breakage Code], 2)),

• Purpose: Adds a column tmpDepartment that extracts the first two characters from the "Breakage Code".

11. Merge with Department Table

    #"Merged Queries2" = Table.NestedJoin(#"Added Custom", {"tmpDepartment"}, #"tbl Department", {"Code"}, "tbl Department", JoinKind.LeftOuter),
    #"Expanded tbl Department" = Table.ExpandTableColumn(#"Merged Queries2", "tbl Department", {"Code", "Department"}, {"tbl Department.Code", "tbl Department.Department"}),

• Purpose: Joins with the "tbl Department" table and expands to include department information.

12. Final Cleanup

    #"Removed Columns4" = Table.RemoveColumns(#"Expanded tbl Department",{"tmpDepartment", "tbl Department.Code"})
in
    #"Removed Columns4"

• Purpose: Removes the temporary columns added during processing to finalize the dataset for use.

---

Conclusion

This Power Query M script effectively reads and processes CSV data related to breakage reports by transforming, cleaning, and enriching the dataset. The methodology employed in each step ensures that the data is accurate and properly structured for subsequent analysis. For further enhancements and learning resources, consider exploring the courses available on the Enterprise DNA Platform.