Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Functioning Columns

31 July 2019

Welcome to our Power Query blog.  Today, I create a function to add a column to a table.

 

Back in Power Query: Splitting Up is Not so Hard to Do, I divided a column containing a full name into first and last name columns, viz.

Since this is a common scenario, I want to create a function that I can apply whenever I have a full name column that I need to split up.

I need to convert the M code I used to split my column into a function.  My original code looked like this:

let

    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Employee",Splitter.SplitTextByDelimiter(" ", QuoteStyle.None),{"Employee.1", "Employee.2"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Employee.1", type text}, {"Employee.2", type text}})

in

    #"Changed Type1"

To begin, I create a new blank query from the ‘From Other Sources’ option of the dropdown from ‘New Query’ on the ‘Get & Transform’ section of the ‘Data’ tab.

I simplify the code and rename my function to fnSplitName.

The M code I have used is:

= (mytable as table,fullname as text, column1 as text, column2 as text) => Table.SplitColumn(mytable,fullname,Splitter.SplitTextByDelimiter(" ", QuoteStyle.None),{column1, column2})

This code transforms a given column into two columns by splitting at the first space.  I can now use it to transform my original data.

When I invoke, I get a new query with my results:

I could also use the function in my query:

When I execute this step, I get the following results:

I could tweak my function to create a copy of the column to be split if I wanted to keep it, and this function will work for any column that I want to split into two using a space.  By adding more parameters, I could make it more flexible, but there is a risk of making the function too complex to be useful.

 

Come back next time for more ways to use Power Query!

Newsletter