Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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"
Solved! Go to 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"
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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
16 | |
10 | |
9 | |
9 | |
8 |
User | Count |
---|---|
23 | |
16 | |
14 | |
12 | |
12 |