Forum Discussion
Need help with CONCATENATE multiple columns in table
Hi All Would like to seek your assistance in finding any easier method/ less tedious method in concatenating my data to obtain what I want? Currently, my approach is to concatenate manually, but ...
Jin_Tang Perhaps Power Query (PQ) is what you need. Are you familiar with that?
Connect to your data. The merged cells in the condition column will be un-merged by PQ, by the way. Then do some clean-up steps and create the desired output. Note, however, that the output is grouped, based on the condition into one cell separating the elements by a line-feed. Wrap text applied to column A in the "output" sheet to see the impact of the line feeds.
- Hi Riny
Thanks for the help.
However for the example of having 2 different inputs for the same conditions, this doesn't seem to work.
Using the example i listed
x1 is the name of the person, x2 is the address, x3 is the telephone, x4 is the company, etc while condition a is the capital
and i have y1, y2, y3, which are detail of another group while they are still group under the same condition a.
then PQ doesnt really solve this problem, since they will group all of them up due to the condition A
Would there be any other ways, i.e. via VBA to concatenate the number of column based on the merged cell?Jin_Tang Okay. With a few extra steps in PQ you can group it as desired. See attached.
- Hi Riny
I have been trying and I have an issue when merging the queries. How were you able to merge when the number of columns of "Custom" does not match the number of columns of "Index"?
This was the formula that you used
= Table.NestedJoin(#"Grouped Rows", {"Custom"}, #"Table1-copy", {"Index"}, "Table1-copy", JoinKind.LeftOuter)
I tried mimicking it on my data and the table display always shows null
