Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Crow2525
Helper I
Helper I

Custom Function Query based on a Column input

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"

 

 

 

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"




View solution in original post

1 REPLY 1
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.