Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
xkapr
New Member

How to split row to n number of rows based on cell value

Need to split table like this:

ab2
cd3

 

to

ab1
ab1
cd1
cd1
cd1

 

thanks for help a suggestions.

1 ACCEPTED SOLUTION
AntrikshSharma
Super User
Super User

@xkapr Here are 2 ways to solve this, paste the whole code in the Advanced Editor in PQ.

 

let
    Source = 
    Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText ( "i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64 ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Text = _t, Text2 = _t, Value = _t ]
    ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "Text", type text }, { "Text2", type text }, { "Value", Int64.Type } }
        ),
    ColumnNames = Table.ColumnNames ( ChangedType ),
    // This step helps in creating the data types for columns before the Expansion
    ColumnAndTypes = 
        Expression.Evaluate (
            "type table ["
                & Text.Combine (
                    List.Transform (
                        Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
                        each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
                    ),
                    ", "
                )
                & "]"
        ),
    AddedCustom = 
        Table.AddColumn (
            ChangedType,
            "Custom",
            each Table.Repeat ( Table.FromRecords ( { _ } ), _[Value] ),
            ColumnAndTypes
        ),
    RemovedColumns = Table.RemoveColumns ( AddedCustom, { "Text", "Text2", "Value" } ),
    ExpandedCustom = 
        Table.ExpandTableColumn (
            RemovedColumns,
            "Custom",
            ColumnNames,
            ColumnNames
        )
in
    ExpandedCustom

 

 

 

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText ( "i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64 ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ Text = _t, Text2 = _t, Value = _t ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "Text", type text }, { "Text2", type text }, { "Value", Int64.Type } }
        ),
    ColumnsAndTypes = 
        Expression.Evaluate (
            "type table ["
                & Text.Combine (
                    List.Transform (
                        Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
                        each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
                    ),
                    ", "
                )
                & "]"
        ),
    Transformation = 
        List.Transform (
            Table.ToRecords ( ChangedType ),
            each Table.Repeat ( Table.FromRecords ( { _ }, ColumnsAndTypes ), _[Value] )
        ),
    Result = Table.Combine ( Transformation )
in
    Result

 

View solution in original post

3 REPLIES 3
AnkitKukreja
Super User
Super User

HI! @xkapr 

 

Please replace this code in the advance editor.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Col3"}, "Attribute", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Value", "Col3", "Attribute"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"})
in
#"Removed Columns"

 

 

For Power BI trainings or support dm or reach out to me on LinkedIn.
If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
AntrikshSharma
Super User
Super User

@xkapr Here are 2 ways to solve this, paste the whole code in the Advanced Editor in PQ.

 

let
    Source = 
    Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText ( "i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64 ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Text = _t, Text2 = _t, Value = _t ]
    ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "Text", type text }, { "Text2", type text }, { "Value", Int64.Type } }
        ),
    ColumnNames = Table.ColumnNames ( ChangedType ),
    // This step helps in creating the data types for columns before the Expansion
    ColumnAndTypes = 
        Expression.Evaluate (
            "type table ["
                & Text.Combine (
                    List.Transform (
                        Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
                        each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
                    ),
                    ", "
                )
                & "]"
        ),
    AddedCustom = 
        Table.AddColumn (
            ChangedType,
            "Custom",
            each Table.Repeat ( Table.FromRecords ( { _ } ), _[Value] ),
            ColumnAndTypes
        ),
    RemovedColumns = Table.RemoveColumns ( AddedCustom, { "Text", "Text2", "Value" } ),
    ExpandedCustom = 
        Table.ExpandTableColumn (
            RemovedColumns,
            "Custom",
            ColumnNames,
            ColumnNames
        )
in
    ExpandedCustom

 

 

 

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText ( "i45WSlTSUUoCYiOlWJ1opWQgKwWIjZViYwE=", BinaryEncoding.Base64 ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ Text = _t, Text2 = _t, Value = _t ]
        ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "Text", type text }, { "Text2", type text }, { "Value", Int64.Type } }
        ),
    ColumnsAndTypes = 
        Expression.Evaluate (
            "type table ["
                & Text.Combine (
                    List.Transform (
                        Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
                        each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
                    ),
                    ", "
                )
                & "]"
        ),
    Transformation = 
        List.Transform (
            Table.ToRecords ( ChangedType ),
            each Table.Repeat ( Table.FromRecords ( { _ }, ColumnsAndTypes ), _[Value] )
        ),
    Result = Table.Combine ( Transformation )
in
    Result

 

Thank you. I injected this to my query.

 

 

let
    Zdroj = Excel.CurrentWorkbook(),
    Dny = Excel.CurrentWorkbook(){[Name="ondrej_dny_cp"]}[Content][Ondřej dny CP],
    #"Filtrované řádky" = Table.SelectRows(Zdroj, each ([Name] = "ondrej_resitel")),
    #"Rozbalené Content" = Table.ExpandTableColumn(#"Filtrované řádky", "Content", {"Partner", "Město", "Upřesnění", "Účel", "Km max", "Skutečná", "Jízd", "Km celkem"}, {"Content.Partner", "Content.Město", "Content.Upřesnění", "Content.Účel", "Content.Km max", "Content.Skutečná", "Content.Jízd", "Content.Km celkem"}),
    #"Filtrované řádky1" = Table.SelectRows(#"Rozbalené Content", each ([Content.Km celkem] <> 0)),
    #"Odebrané: Dolní řádky" = Table.RemoveLastN(#"Filtrované řádky1",1),
    #"Odebrané sloupce" = Table.RemoveColumns(#"Odebrané: Dolní řádky",{"Name"}),
    #"Přidané: Vlastní" = Table.AddColumn(#"Odebrané sloupce", "JízdSoučet", each [Content.Skutečná]+[Content.Jízd]),
    #"Přeuspořádané sloupce" = Table.ReorderColumns(#"Přidané: Vlastní",{"Content.Partner", "Content.Město", "Content.Upřesnění", "Content.Účel", "Content.Km max", "Content.Skutečná", "Content.Jízd", "JízdSoučet", "Content.Km celkem"}),
    #"Odebrané sloupce1" = Table.RemoveColumns(#"Přeuspořádané sloupce",{"Content.Skutečná", "Content.Jízd"}),
    DoKnihyJizd = Table.RenameColumns(#"Odebrané sloupce1",{{"Content.Partner", "Partner"}, {"Content.Město", "Město"}, {"Content.Upřesnění", "Upřesnění"}, {"Content.Účel", "Účel"}, {"Content.Km max", "Km"}, {"Content.Km celkem", "Km celkem"}}),
    DoKnihy= Table.SelectColumns(DoKnihyJizd,{"JízdSoučet"}),


    ChangedType = 
        Table.TransformColumnTypes (
            DoKnihy,
            { { "JízdSoučet", Int64.Type } }
        ),
    ColumnsAndTypes = 
        Expression.Evaluate (
            "type table ["
                & Text.Combine (
                    List.Transform (
                        Table.ToRecords ( Table.Schema ( ChangedType )[[Name], [Kind]] ),
                        each Expression.Identifier ( [Name] ) & " = nullable " & [Kind]
                    ),
                    ", "
                )
                & "]"
        ),
    Transformation = 
        List.Transform (
            Table.ToRecords ( ChangedType ),
            each Table.Repeat ( Table.FromRecords ( { _ }, ColumnsAndTypes ), _[JízdSoučet] )
        ),
    Result = Table.Combine ( Transformation )




in
Result

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.