Using PowerQuery to extract particular record from a column to another column

Question

I'm trying to extract particular record from a body column and update into separate columns using PowerQuery.

Scenario: I'm fetching records from Microsoft Exchange into an Excel file, separated into columns like "DateTimeReceived", "Subject", "Body". The Body column contains multiple records firstname, lastname, email & cost center.

Requirement: I want to extract records from "Body" column to 4 separate columns such as "First Name", "Last Name", "Email", "Cost Center" using Excel PowerQuery.

Steps I performed:

Split by delimiter -":"
Split by delimiter -" "
Adding a custom column using "Text.Contains"

Here are screenshots from the table:

E-Mail fetched with body:

After delimiting body:

It seems you have the result you want. What is your question? — Ron Rosenfeld
– Ron Rosenfeld, Commented Nov 8 at 11:58

ThxAlot · Accepted Answer · 2025-11-09 21:09:39Z

As long as the format is in line with your mockup data, it's easy to tranform,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5BC8IwDIX/Suh5dLZzIjuZdu4whic9rTvUUWbBtrDt/2PrYaggCQkJj/e9vieM5cec73hJMtKFycbV2HlZ4aKdqQCFrOMQynd6e8oalT87bZ9JwHixP+n7SMfglJchyqTxq5krYGXBGRmyN+bwg1Hr9WEXiK09mOQGlFLlP/EtygZB4O2LL1qMtUUQ6UL5L0OMR4bhBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTimeReceived = _t, Subject = _t, Body.TextBody = _t]),
    #"Split Column" = Table.TransformColumns(
        Source,
        {"Body.TextBody",
        each let
            // remove email
            txt = Text.RemoveRange(_, 0, Text.PositionOf(_, "First Name: ", 0)),
            // split headers
            l = List.Zip(List.Transform(Text.Split(txt, "#(lf)"), each Text.Split(_, ": ")))
        in Record.FromList(l{1}, l{0})}
    ),
    #"Expanded Body.TextBody" = Table.ExpandRecordColumn(#"Split Column", "Body.TextBody", {"First Name", "Last Name", "Email", "Cost Center"})
in
    #"Expanded Body.TextBody"

kb2136 · Accepted Answer · 2025-11-08 09:21:07Z

Let's assume this is your starting point:

DateTimeReceived	Subject	Body.TextBody
11/8/2025	Logi	First Name: ABCD ABB Last Name: CDA Email: [email protected] Cost Center: 15321
11/6/2025	Logi	This is an email First Name: JACFA BAU Last Name: BJAJAJ Email: [email protected] Cost Center: 1234

Then one possible solution would be:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // load data from Excel table
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Body.TextBody", "tmp"), // create a temporary column to be manipulated below
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"tmp", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "tmp"), // split the tmp column into multiple rows, wherever a line break occurs
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", 
                            each Text.StartsWith([tmp], "First Name")
                            or Text.StartsWith([tmp], "Last Name")
                            or Text.StartsWith([tmp], "Email")
                            or Text.StartsWith([tmp], "Cost Center")
        ), // this will remove values like "This is an email", which are useless in your scenario
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Filtered Rows", "tmp", Splitter.SplitTextByEachDelimiter({": "}, QuoteStyle.Csv, false), {"LABEL", "VALUE"}), // split the tmp column into two new columns, using the leftmost ": " as a separator
    #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter1"), // remove duplicate rows (if any), to avoid errors in the next step 
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[LABEL]), "LABEL", "VALUE") // pivot the table WITHOUT AGGREGATION (that's why it's important to make sure there are no duplicates)
in
    #"Pivoted Column"

Storax · Accepted Answer · 2025-11-08 10:44:50Z

I would suggest to use the following function GetFieldValue to extract the text after the fields like "First Name", "Last Name" etc.

(fieldName as text, inputText as text) =>
let
    // Split the input text into lines
    Lines = Text.Split(inputText, "#(lf)"),

    // Find the line that starts with the field name followed by ":"
    MatchingLine = List.First(List.Select(Lines, each Text.StartsWith(_, fieldName & ":"))),

    // Extract the value after the colon and optional space
    Value = Text.Trim(Text.AfterDelimiter(MatchingLine, ":"))
in
    Value

You can add a user defined column for each field and call the function like that in case "First Name"

= Table.AddColumn(pqStep, "FirstName", each GetFieldValue("First Name", [Body.TextBody]))
pqStep is the name of the previous Power Query step.

Assuming your workbook looks like you posted , complete M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    pqStep = Table.TransformColumnTypes(Source,{{"DateTimeReceived", type datetime}, {"Subject", type text}, {"Body.TextBody", type text}}),
    addFirstName = Table.AddColumn(pqStep, "FirstName", each GetFieldValue("First Name", [Body.TextBody])),
    addLastName= Table.AddColumn(addFirstName, "LastName", each GetFieldValue("Last Name", [Body.TextBody])),
    addEmail = Table.AddColumn(addLastName, "Email", each GetFieldValue("Email", [Body.TextBody])),
    addCostCenter = Table.AddColumn(addEmail, "CostCenter", each GetFieldValue("Cost Center", [Body.TextBody]))
in
    addCostCenter

Another approach is splitting and filtering the input accordingly.

let
    // Sample input text
    Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],

    // Split the text into rows by line feed
    SplitLines = Table.ExpandListColumn(
        Table.TransformColumns(Source, {
            "Body.TextBody", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv)
        }), "Body.TextBody"
    ),
    FilterRows = Table.SelectRows(SplitLines, each ([Body.TextBody] <> "")),
    SplitFields = Table.SplitColumn(FilterRows, "Body.TextBody", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.None, false), {"Body.TextBody.1", "Body.TextBody.2"}),
    FilterFields = Table.SelectRows(SplitFields, each [Body.TextBody.1] = "First Name" or [Body.TextBody.1] = "Last Name" or [Body.TextBody.1] = "Email" or [Body.TextBody.1] = "Cost Center"),
    Pivot = Table.Pivot(FilterFields, List.Distinct(FilterFields[Body.TextBody.1]), "Body.TextBody.1", "Body.TextBody.2")
in
    Pivot

Ron Rosenfeld · Accepted Answer · 2025-11-08 12:47:41Z

Using this method we add a single custom column which contains a List of Records, then expand that list into the additional columns.

We assume that the labels are known and do not vary in either case or spelling. If they do, some modifications may be required.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"DateTimeReceived", type datetime}, {"Subject", type text}, {"Body.TextBody", type text}}),
    
    #"Add Splitted Column" = Table.AddColumn(#"Changed Type", "Split", (r)=>
        [a=Text.SplitAny(r[Body.TextBody],":,#(lf)"),
         b=List.RemoveItems(a,{""}),
         c=List.Transform(b, each Text.Trim(_," ")),
         d={"First Name","Last Name", "Email","Cost Center"},
         e=List.Transform(d, each List.PositionOf(c,_)+1),
         f=List.Transform(e, each c{_}),
         g=Record.FromList(f,d)][g]),
    
    #"Removed Columns" = Table.RemoveColumns(#"Add Splitted Column",{"Body.TextBody"}),
    #"Expanded Split" = Table.ExpandRecordColumn(#"Removed Columns", "Split", {"First Name", "Last Name", "Email", "Cost Center"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Split",{
        {"First Name", type text}, {"Last Name", type text}, {"Email", type text}, {"Cost Center", Int64.Type}})
in
    #"Changed Type1"

Result from your data:

Collectives™ on Stack Overflow

Using PowerQuery to extract particular record from a column to another column

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related