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

[xkapr]

Need to split table like this:

a	b	2
c	d	3

to

a	b	1
a	b	1
c	d	1
c	d	1
c	d	1

thanks for help a suggestions.

[AnkitKukreja]

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"

[AntrikshSharma · Accepted solution]

@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

[xkapr]

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