concatenate the last column records to first column record

[vidyasagar159]

Hello All,

Need some help with my below-reporting view format. 

Data:

Name	color	Category
Apple	Red	Fruit
Apple	green	Fruit
Grape	white	Fruit
Grape	black	Fruit
Onion	white	veggi
Onion	Red	veggi
Pepper	Red	Veggi
Pepper	green	veggi

Expected
Name	color
Fruit
Apple	Red
Apple	green
Grape	white
Grape	black
veggi
Onion	white
Onion	Red
Pepper	Red
Pepper	green

[camargos88]

@vidyasagar159 ,

I had to code on Advanced Editor.

[vidyasagar159]

@camargos88 

Great response!!!. For the below-grouped rows did you manually wrote the script in the editor? 

= Table.Group(#"Capitalized Each Word", {"Category"}, {{"Rows", each let _category = Table.FromList(List.Distinct(_[Category]), Splitter.SplitByNothing(), {"Name"}) in Table.Combine({_category, _}), type table}})

[camargos88 · Accepted solution]

@vidyasagar159 ,

Try this mcode:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUUrOz8kvAtLOiSWp6flFlUqxOtFKjgUFOSDJoNQUIOlWVJpZgiKeXpSamoci416UWACSKc/ILEnFKpOUk5icjSLjn5eZn4ekpyw1PT0TRQZiP0I8ILWgILUILhGGKQFzGVRPLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"color", type text}, {"Category", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"Category", Text.Proper, type text}}),
    #"Grouped Rows" = Table.Group(#"Capitalized Each Word", {"Category"}, {{"Rows", each let _category = Table.FromList(List.Distinct(_[Category]), Splitter.SplitByNothing(), {"Name"}) in Table.Combine({_category, _}), type table}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"Name", "color"}, {"Name", "color"})
in
    #"Expanded Rows"