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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 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
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors