Forum Discussion

clemente's avatar
clemente
Copper Contributor
Mar 24, 2025

Spliting rows in Excel with Power Query

Hello all,

I would like to know if there is a way to split rows depending on the value of one or more cells using DAX or Power Query instead of Visual Basic

Here's an example:

LineIDDateAffected Units
1CET-75223-dez-2024GET5-SO
2CET-76324-dez-2024GET3-SE
3CET-76425-dez-2024GET4-SU, GET8-OE, GET5-SO

Considering Line 3, I would like to create two additional rows splitting cell D3 using comma as a separating value. The results I would like to achieve is:

LineIDDateAffected Units
1CET-75223-dez-2024GET5-SO
2CET-76324-dez-2024GET3-SE
3CET-76425-dez-2024GET4-SU
4CET-76425-dez-2024GET8-OE
5CET-76325-dez-2024GET5-SO

Thank you in advance!

 

BI & Data Analysis
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 26, 2025

    As variant

    let
    Source = Excel.CurrentWorkbook(){[Name="DemoOne"]}[Content],
    RemoveBlankColumn = Table.RemoveColumns(Source,{"Column1"}),
    
    SplitText = Table.ReplaceValue(
        RemoveBlankColumn,
        each [Affected Units] ,
        each Text.Split( [Affected Units], ", " ),
        Replacer.ReplaceValue,{"Affected Units"}),
    ExpandLists = Table.ExpandListColumn(SplitText, "Affected Units"),

    AdjustLine = Table.FromColumns(
        {{1..Table.RowCount( ExpandLists )}} &
        List.Skip( Table.ToColumns( ExpandLists ) ),
        Table.ColumnNames(ExpandLists)
    ),
    DeclareType = Table.TransformColumnTypes(
        AdjustLine,
        {
              {"Line", Int64.Type}
            , {"ID", type text}
            , {"Date", type date}
            , {"Affected Units", type text}
        })
in
    DeclareType

Resources