I am wanting to combine multiple columns into one. The issue being that The number of columns I want to combine will vary depending on the data I import into my flow. What I need is the ability to say something like if the column header contains “” then combine into new column. I can’t find anything that will do this, or any work around. Does anyone have any ideas?
Wanted to flag the concept laid out in this community post with you: How to delete empty columns / conditional delete of column basic on text string?
I’d look into combining these steps with the
Merge duplicate rows step. Hope this helps – let us know how this approach works for you!
Hi @Adam_R ,
Thanks for your email, unfortunately I don’t think that will work, or I can’t get it to work.
I will try and explain it better. I have multiple recipes being imported from bubble, (max 10, min 1 recipes), each recipe comes with its own column of ingredients, the recipes will have some ingredients that are the same and some that are different, so when I am combining these I end up with multiple columns of ingredients, which I am wanting to combine into one column, (i will have to do the same with the price of each ingredient). Unfortunately when I import the data from bubble I might only have 2 recipes, so two columns of ingredients, 10 recipes or somewhere in between, so I can’t just set up a static list of columns I need to combine and delete, it needs to be a bit more fluid. that is why a combine column action where you can say, if any column header contains the “ingredient” combine column.
Have to setup a call to explain further if needed.
You may be able to accomplish this using a “Pivot Columns” step. I’ve mocked out a basic template of the flow below:
In the dummy data that I’ve pulled in from Google Sheets, there is a list of recipes that have ingredients in multiple columns. We’ll want to go ahead and combine these together:
Using the “Unpivot columns” step, we can combine multiple columns into a single column based on a set of rules. In this case, I’ve selected my
Recipe column as the unique identifier column and switched the Pivot type to
All Columns Except. You can leave the pivot columns value blank.
The resulting table will combine all of your columns into a single column. From there, use an “Insert if/else column” step to target the
Type column and check if the value contains
Ingredient. If so, create a new column and set the value to
Next use a “Filter rows” step to
Keep rows where the value in our new column is equal to
Lastly, we can use a “Merge duplicate rows” step to merge the
Value column containing the ingredients based on the
If you need to merge that data back with your original table, you can use the “Combine tables” step to merge on a shared
Hope that helps!
Thanks for your email. That does work, unfortunately it doesn’t work for my data. Below is screenshot of how my data presents. Not only is there multiple columns for ingredients, but there are multiple columns for ingredient prices. Then there are percentage columns that need to be kept seperate.
Hard to explain in an email, but have a look at the screenshot and let me know if you have any further questions. I can get it to do what I want it to do with a work around that I have sorted. Unfortunately the work around only works if it is a set amount of recipes that I am using, and that there will be no change to the number of recipes from file to file that gets imported from bubble. Unfortunately that is not going to happen.
Just sent you a separate email. Let’s find a time in the future to discuss this over a call. Hope to connect soon!
Booked in for Thursday next week.