Apr 07 2021 06:42 PM
Hello All,
I have power automate triggering tasks and filling in a spreadsheet when certain emails come through.
It's basically to track actions with strict timeframes. Power automate can filter out replies, and using Power query in excel, I can create a new table that extracts the relevant names from the email title.
The emails can come in with 2 names, not just one, so the columns are Name 1, Name 2, Due Date, and Reminder Date.
For tracking purposes and to work with other sheets, it would be better if the 2 Names columns were one, but still matched to the correct dates. See pic below.
(You can ignore the blank name rows, they were earlier tests. All future emails will have name in the title. The email title will always be structured "Particular phrase for powerautomate to pick up; Name 1; Name 2")
Any suggestions?
Apr 07 2021 10:38 PM
This solves the issue:
{=IFERROR(INDEX($A$96:$A$103, SMALL(IF(ISBLANK($A$96:$A$103), "", ROW($A$96:$A$103)-MIN(ROW($A$96:$A$103))+1), ROW(A1))), IFERROR(INDEX($B$96:$B$103, SMALL(IF(ISBLANK($B$96:$B$103), "", ROW($B$96:$B$103)-MIN(ROW($B$96:$B$103))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK($B$96:$B$103)))))), ""))}
N.B.
Apr 07 2021 10:57 PM - edited Apr 07 2021 10:58 PM
@Davidm54 Since you are already using Power Query, you can unpivot the two name columns and perform a few clean-up steps. Example attached.
Apr 08 2021 01:56 AM
Like @Riny_van_Eekelen I suspect that waiting until the data is loaded into Excel in an unwanted format is a little late in the game to start making adjustments. That said Excel 365 is evolving to became a very different programming platform from traditional spreadsheets and techniques are becoming available that will allow you either to transform the data or simply use it as it stands.
Apr 08 2021 12:58 PM
@Peter Bartholomew , what's the reason to use formulas on the top of returned by Power Query table if you may add couple of more steps to already existing query?
Apr 08 2021 01:42 PM
Apr 08 2021 07:44 PM