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: Line ID Date ...
BI & Data Analysis
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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