Custom Function Query based on a Column input

[Crow2525]

Objective:

A custom function that takes a column input and outputs a table of columns. 

Why:

I repeat these steps all the time in different data projects and would like to transfer them across projects.

Examples:

1. Take a column, Table[policy number], as input/parameter.  Extract the first two columns, call it branch code.  Extract the last three columns and call it policy type.  Utilise the branch code in a left join merge with an existing query to expand more columns.

2. Take a column, Table[account number], as input/parameter.  Merge the column parameter with another query and expand the columns.

This query works, but it's not parameterised. 

let     
Source = #"Inforce Prepare Script"[Policy Number],
ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Inserted First Characters" = Table.AddColumn(Source, "Branch Code", each Text.Start([Column1], 2), type text),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted First Characters", "Policy Type", each Text.End([Column1], 3), type text),
#"Merged Queries" = Table.NestedJoin(#"Inserted Last Characters", {"Branch Code"}, _BranchCodetoDivision, {"Branch Code"}, "_BranchCodetoDivision", JoinKind.LeftOuter),
    #"Expanded _BranchCodetoDivision" = Table.ExpandTableColumn(#"Merged Queries", "_BranchCodetoDivision", {"Division", "Division Code", "Business Unit", "Bus Unit Code", "Region", "Region Code", "Sub-Region", "Sub-Region Code", "Branch", "State-Detailed", "State"}, {"Division", "Division Code", "Business Unit", "Bus Unit Code", "Region", "Region Code", "Sub-Region", "Sub-Region Code", "Branch", "State-Detailed", "State"})
in 
#"Expanded _BranchCodetoDivision"

This is my attempt:

= (PolicyNumberCol as list) =>

let
Source = PolicyNumberCol,
ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Inserted First Characters" = Table.AddColumn(ConvertToTable, "Branch Code", each Text.Start([Column1], 2), type text),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted First Characters", "Policy Type", each Text.End([Column1], 3), type text),
#"Merged Queries" = Table.NestedJoin(#"Inserted Last Characters", {"Branch Code"}, _BranchCodetoDivision, {"Branch Code"}, "_BranchCodetoDivision", JoinKind.LeftOuter),
#"Expanded _BranchCodetoDivision" = Table.ExpandTableColumn(#"Merged Queries", "_BranchCodetoDivision", {"Division", "Division Code", "Business Unit", "Bus Unit Code", "Region", "Region Code", "Sub-Region", "Sub-Region Code", "Branch", "State-Detailed", "State"}, {"Division", "Division Code", "Business Unit", "Bus Unit Code", "Region", "Region Code", "Sub-Region", "Sub-Region Code", "Branch", "State-Detailed", "State"})
in
#"Expanded _BranchCodetoDivision"

[Daryl-Lynch-Bzy · Accepted solution]

HI @Crow2525  - you have got to love insurance!  try this:  I have added "as table"

(PolicyNumberCol as list) as table =>

let
Source = PolicyNumberCol,
ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Inserted First Characters" = Table.AddColumn(ConvertToTable, "Branch Code", each Text.Start([Column1], 2), type text),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted First Characters", "Policy Type", each Text.End([Column1], 3), type text),
#"Merged Queries" = Table.NestedJoin(#"Inserted Last Characters", {"Branch Code"}, _BranchCodetoDivision, {"Branch Code"}, "_BranchCodetoDivision", JoinKind.LeftOuter),
#"Expanded _BranchCodetoDivision" = Table.ExpandTableColumn(#"Merged Queries", "_BranchCodetoDivision", {"Division", "Division Code", "Business Unit", "Bus Unit Code", "Region", "Region Code", "Sub-Region", "Sub-Region Code", "Branch", "State-Detailed", "State"}, {"Division", "Division Code", "Business Unit", "Bus Unit Code", "Region", "Region Code", "Sub-Region", "Sub-Region Code", "Branch", "State-Detailed", "State"})
in
#"Expanded _BranchCodetoDivision"