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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Jamie666stuck
Frequent Visitor

Merge rows with delimiter

Hi all. I am currently working on a project where I need to merge rows in a table i.e an individual could have 2 rows/returns. I need them to show as 1 row but where they have given an different answer for the coloumn merge with a deliminater as per example.

 

Current table named "New format"

 

Name   Fruit       Cakes.    Meat

Joe.       Apple.   Sponge   Bacon

Joe.                     Fairy      

 

Required

 

Name.    Fruit.   Cakes.              Meat

Joe.        Apple.  Sponge;Fairy  Bacon

 

Any help would be appreciated

 

Thank you

 

 

 

1 ACCEPTED SOLUTION

@Jamie666stuck In that case only use the code from GroupedRows step onwards, for clarity just create a new blank query > go to advanced editor and then use my complete code.

View solution in original post

4 REPLIES 4
JamesRobson
Resolver II
Resolver II

Not sure if this will help but this is how I do it, replace the bold text with your specific data and paste into advanced editor at the correct point. Step3 seperates the answers into different columns so delete that if you want them in the same column.

 

This example would create a single line for each ItemCode (Name) and multiple columns for each Ink (Cake) 

Splitting multiple Rows into sperate columns based on a header i.e. ItemCode and Inks

    #"SplitStep1" = Table.Group(#"PreviousStep", {"ItemCode"}, {{"Count", each Table.RowCount(_), type number},{"Inks", each Text.Combine([Inks],","), type text}}),

    Columns = List.Transform({1..List.Max(#"SplitStep1"[Count])}, each "Inks."&Text.From(_)),

    #"SplitStep2" = Table.SplitColumn(#"SplitStep1", "Inks", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), Columns),

    #"SplitStep3" = Table.RemoveColumns(#"SplitStep2",{"Count"})

 

 

Thanks,

AntrikshSharma
Super User
Super User

@Jamie666stuck Paste this in the advanced editor:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45W8spPVdJRciwoyAHRwQX5eekghlNicn6eUqwOTAEQuSVmFlWCmCBR38xsiLI8IAQynDPyk/NzEktSwezM5OzUPGR1vol56flAOiwxLzMnJxFsSiwA",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Name = _t, Fruit = _t, Cakes = _t, Meat = _t ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "Name", type text },
            { "Fruit", type text },
            { "Cakes", type text },
            { "Meat", type text }
        }
    ),
    GroupedRows = Table.Group (
        ChangedType,
        { "Name" },
        {
            {
                "Transformation",
                each Table.FromRows (
                    {
                        List.Transform (
                            Table.ToColumns ( Table.RemoveColumns ( _, "Name" ) ),
                            each Text.Combine ( List.RemoveItems ( _, { "" } ), "; " )
                        )
                    },
                    type table [ Fruit = text, Cakes = text, Meat = text ]
                ),
                type table [ Fruit = text, Cakes = text, Meat = text ]
            }
        }
    ),
    ExpandedTransformation = Table.ExpandTableColumn (
        GroupedRows,
        "Transformation",
        { "Fruit", "Cakes", "Meat" },
        { "Fruit", "Cakes", "Meat" }
    )
in
    ExpandedTransformation

 hi I pasted it in but suspect my previous code in there is stopping it, could you advise what I need to tweak? Thank you

IMG_20221117_144411.jpg

 

@Jamie666stuck In that case only use the code from GroupedRows step onwards, for clarity just create a new blank query > go to advanced editor and then use my complete code.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors