Forum Discussion

MuhammadYasir's avatar
MuhammadYasir
Copper Contributor
Jul 26, 2020
Solved

add raw data in one cell

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     
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 27, 2020

    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"
mathetes's avatar
mathetes
Gold Contributor
Jul 27, 2020

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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 27, 2020

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"

Resources