Jul 26 2020 11:41 PM
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
Jul 27 2020 06:39 AM
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.
Jul 27 2020 01:28 PM
SolutionCouple 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"
Jul 27 2020 01:28 PM
SolutionCouple 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"