Forum Discussion
Solved
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 answ...
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.
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
ExpandedTransformationhi I pasted it in but suspect my previous code in there is stopping it, could you advise what I need to tweak? Thank you
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.
