Power Query: Extracting Left 2 Characters From a Column

February 25, 2020 - by Bill Jelen

In my original video about reshaping data, I arrived at a point where I needed to get the first 2 characters from a column. My method involved Split Column to generate the first 2 characters and everything else. I then deleted everything else.

Mike Girvin from the ExcelisFun channel, as well as Brandon Brimberry, Wyn Hopkins from Access Analytic, Geert Demulle noted that a faster way would be to use the Transform, Extract, First Characters.

This keeps just the quarter information with no need to delete the rest of the column.

Bohdan Duda used Extracted Text Before Delimiter to get everything before the _ character.

Mike Girvin noted that his solution was “on the fly, quick before I am out the door.” Here is his code:

let
    Source = Excel.CurrentWorkbook(){[Name="BadStartData"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Category Description"}, "Quarter", "Amount"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if not Text.StartsWith([Quarter],"Q") then [Quarter] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Employee"}),
    #"Extracted First Characters" = Table.TransformColumns(#"Filled Down", {{"Quarter", each Text.Start(_, 2), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted First Characters", each Text.StartsWith([Quarter], "Q")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Quarter]), "Quarter", "Amount", List.Sum),
    #"Filtered Rows1" = Table.SelectRows(#"Pivoted Column", each ([Employee] <> "Dept. Total")),
    #"Inserted Sum" = Table.AddColumn(#"Filtered Rows1", "Total", each List.Sum({[Q1], [Q2], [Q3], [Q4]}), type number),
    #"Sorted Rows" = Table.Sort(#"Inserted Sum",{{"Employee", Order.Ascending},{"Category Description", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Q1", type number}, {"Q2", type number}, {"Q3", type number}, {"Q4", type number}, {"Total", type number}, {"Employee", type text}, {"Category Description", type text}})
in
    #"Changed Type"

Here is Geert DeMulle's solution:

let
    Source = Excel.CurrentWorkbook(){[Name="tblUglyData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category Description", type text}, {"Dept. Total", type number}, {"Q1", type number}, {"Q2", type number}, {"Q3", type number}, {"Q4", Int64.Type}, {"Employee 1", type number}, {"Q12", type number}, {"Q23", type number}, {"Q34", Int64.Type}, {"Q45", Int64.Type}, {"Employee 2", Int64.Type}, {"Q16", Int64.Type}, {"Q27", Int64.Type}, {"Q38", Int64.Type}, {"Q49", Int64.Type}, {"Employee 3", Int64.Type}, {"Q110", Int64.Type}, {"Q211", Int64.Type}, {"Q312", Int64.Type}, {"Q413", Int64.Type}, {"Employee 4", type number}, {"Q114", type number}, {"Q215", type number}, {"Q316", type number}, {"Q417", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Dept. Total", "Q1", "Q2", "Q3", "Q4"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Category Description"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.StartsWith([Attribute], "Employee") then [Attribute] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Employee"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each Text.StartsWith([Attribute], "Q")),
    #"Extracted First Characters" = Table.TransformColumns(#"Filtered Rows", {{"Attribute", each Text.Start(_, 2), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters",{{"Attribute", "Quarter"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Quarter]), "Quarter", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Employee", type text}}),
    #"Inserted Sum" = Table.AddColumn(#"Changed Type1", "Total", each List.Sum({[Q1], [Q2], [Q3], [Q4]}), type number),
    #"Sorted Rows" = Table.Sort(#"Inserted Sum",{{"Employee", Order.Ascending}, {"Category Description", Order.Ascending}})
in
    #"Sorted Rows"

Return to the main page for the Podcast 2316 challenge.

Read the next article in this series: Power Query: Adding a Total Column.