cancel
Showing results for 
Search instead for 
Did you mean: 
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
Community Champion
Community Champion

@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"

 

 

If I have answered your question or solved your problem, please mark this post as solved.
If you liked my response, please give Kudos

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
AntrikshSharma
Community Champion
Community Champion

@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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors