I am using Microsoft Office 365.
I have an exercise database. Exercises can be arranged in a sequence to create a recipe of exercises. Each recipe has a Recipe ID (which includes the sequence number for the exercises included in the recipe).
I would like to Textjoin all the exercises for a particular Recipe but can't quite make it work.
Ideally my Textjoin would produce something looking like this:
Recipe Name (including the 1st 4 numerals from the recipe ID): 1st Exercise, 2nd Exercise, 3rd Exercise, 4th Exercise, 5th Exercise etc.
e.g. HipStrength 1000: 1st Exercise, 2nd Exercise, 3rd Exercise, 4th Exercise, 5th Exercise...
HipStrength 1000: 1st Exercise, 2nd Exercise, 3rd Exercise, 4th Exercise, 5th Exercise...
ShoulderStrength 1009: 1st Exercise, 2nd Exercise, 3rd Exercise, 4th Exercise, 5th Exercise, 6th Exercise, 7th Exercise...
Attached File
See attached file which includes the ideas on the right of the table about formulas that would help me to achieve this.
Your ideas would be appreciated.
Hi Dominic,
No file attached.
After selecting the file, click the Start Upload button and wait for it to upload fully.
Phil
Ok. Try now.
See the attached whereby I split the first column in Power Query and then exported the query to the Data Model. This then allowed the creation of two measures to concatenate the data in a pivot table.
Thanks. That was interesting. That produced the ingredients in the recipe but also repeated the name of the recipe multiple times (which won't be useful unfortunately).
I was hoping for something simpler that would happen almost automatically.
You could use something like:
=LET(ID,LEFT(Table1[@[Recipe ID & Sequence]],4),IF(ID=LEFT(A1,4),"",Table1[@[Recipe Name]]&" "&ID&":"&TEXTJOIN(", ",1,IF(LEFT(Table1[Recipe ID & Sequence],4)=ID,Table1[Exercises],""))))
I'm not sure if you wanted exercises repeated in the result where they appear more than once in the table?
Thanks so much. That is working really nicely.
