Excel拆分单列(Node Area)数据并保留其他列数据的解决方案求助

Hi everyone, I’m stuck with a daily data processing task and could use some help here! Let me break down what I need to do:

I receive daily data where I need to split the values in Column B (Node Area) into individual rows, while keeping all other columns' data matched to each split row.

I’ve tried two approaches so far but hit roadblocks with both:

1. Power Query Attempt (Not Working) #

I figured Power Query would be the right tool for this, but when I tried splitting Column B by carriage return, nothing happened. Here’s the M code I used:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Available Days", type any}, {"Node Area", type text}, {"Tech Name", type text}, {"Cell Number", type text}, {"MA", type text}, {"Title", type text}, {"Work Type", type text}, {"Pick Up Location", type text}, {"Pickup Time", type time}, {"Truck #", type any}, {"MT Skill Sets", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Node Area", Splitter.SplitTextByDelimiter("#(#)(cr)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Node Area"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Node Area", type text}})
in
    #"Changed Type1"

Fix for Power Query #

The issue is with how you defined the carriage return delimiter. Instead of #(#)(cr), use the proper Power Query newline syntax or the built-in newline splitter. Here’s the corrected code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Available Days", type date}, {"Node Area", type text}, {"Tech Name", type text}, {"Cell Number", type text}, {"MA", type text}, {"Title", type text}, {"Work Type", type text}, {"Pick Up Location", type text}, {"Pickup Time", type time}, {"Truck #", type text}, {"MT Skill Sets", type text}}),
    // Use Splitter.SplitTextByNewlines to handle both carriage return (#(cr)) and line feed (#(lf))
    #"Split Node Area" = Table.TransformColumns(#"Changed Type", {{"Node Area", Splitter.SplitTextByNewlines(), type list}}),
    #"Expand Node Area" = Table.ExpandListColumn(#"Split Node Area", "Node Area"),
    #"Clean Node Area" = Table.TransformColumns(#"Expand Node Area", {{"Node Area", Text.Trim, type text}}) // Optional: trim any extra spaces
in
    #"Clean Node Area"

What changed:

• Replaced the incorrect delimiter with Splitter.SplitTextByNewlines() which automatically handles both #(cr) and #(lf) line breaks
• Added an optional Text.Trim step to clean up any extra spaces in split Node Area values
• Adjusted the data type for "Truck #" to text to avoid issues with "All of the above"

2. Excel Formula Attempt (Partial Success) #

I got a formula working to split just Column B:

=TRANSPOSE(TEXTSPLIT(B3,CHAR(10)))

But I can’t figure out how to extend this to keep all other columns aligned with the split rows.

Full Formula Solution (Office 365/Excel 2021+) #

If you have access to dynamic array functions, use this formula to create the full expanded table (paste into a blank cell, e.g., A8):

=LET(
    originalData, A3:L5, // Replace with your data range
    nodeColumn, INDEX(originalData,,2),
    // Split each Node Area value by line break
    splitNodes, BYROW(nodeColumn, LAMBDA(x, TEXTSPLIT(x, CHAR(10)))),
    // Get number of split rows for each original row
    rowCounts, BYROW(splitNodes, LAMBDA(x, ROWS(x))),
    // Expand each row to match the split Node count, combining all columns
    expandedTable, REDUCE("", SEQUENCE(ROWS(originalData)), LAMBDA(result, currentRow,
        VSTACK(result, HSTACK(
            INDEX(originalData, currentRow, 1), // Available Days
            INDEX(splitNodes, currentRow, ), // Split Node Area values
            INDEX(originalData, currentRow, 3):INDEX(originalData, currentRow, COLUMNS(originalData)) // All other columns
        ))
    )),
    // Remove the blank header row created by REDUCE
    DROP(expandedTable, 1)
)

How it works:

• LET makes the formula easier to read and edit
• BYROW splits each Node Area value into a list
• REDUCE + VSTACK expands each original row into multiple rows, matching the number of split Node Area values
• All other columns are duplicated for each split row automatically

Example Data #

Starting Data

After Editing

Anyone have other suggestions or spot anything I missed? I’d love to get this working to save time on daily manual processing!

备注：内容来源于stack exchange，提问作者Kavorka