add raw data in one cell

[MuhammadYasir]

Hi Team,

i need your help that i have data in my sheet i want to add in one cell with the help of power query excel does any one help me out files is attached.

Thanks

Yasir 

[#2]

@MuhammadYasir 

If what you have is laid out just like this little sample, you don't really need Power Query. A simple TEXTJOIN function could do it put those two cells together. For example, put this in Cell B3

=TEXTJOIN(" ",1,Table2[@[Original Data]],A4)   

But that would still leave you with a blank row, which you probably don't want. So why don't you give us the bigger picture, the context and the purpose you're wanting to achieve beyond just putting those two bits of text together.

[#3 · Accepted solution]

@mathetes 

Couple of more variants if put data in separate table without empty rows:

1) Create new table and use formula for it like

=IFERROR(INDEX(Table2[Original Data], (ROW()-ROW(Table3[[#Headers],[Output]]))*2-1) & " " &
 INDEX(Table2[Original Data], (ROW()-ROW(Table3[[#Headers],[Output]]))*2),"")

2) With Power Query add Index column starting from 0, integer divide it on 2, group on that column and slightly modify formula for grouping in formula bar as in script below, remove index column

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"Output", each Text.Combine( _[Original Data], " ")}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"})
in
    #"Removed Columns"

[#4]

Thanks @mathetes