Latest Enterprise DNA Initiatives

How to append records based on conditional criteria

I receive some data from 10 vendors in diff sheets on daily basis, I have a power automate flow which captures the files and put it a folder (the folder is named based on the date on which the sheets are received)

(dummy data is attached here for appending)

i.e. if I receive the sheets today on 16th May from 10 vendors, the files will be automatically stored in a folder called “16-05-2021”.

My requirements are

  1. PowerBI should pick files from the latest folder based on current date i.e. (today’s files from folder called “16-05-2021”) and append all the records in a new query
  2. if any vendor fails to send the data up to 5 PM, I need to append data from yesterday’s folder (or the recent one) for that particular vendor.

i.e. on 16-05-2021 Vendor3 failed to send the data, so it should take recent data from “15-05-2021” folder, so total Row Count after appending the records on 16th May should be 18 (Vendor1 = 7 + vendor2=7 + Vendor3 records from 15-05-2021 folder)

Please give me some suggestions / work around with this problem statement (32.6 KB)

Hi @suhvishal,

See if something like this can work for you.

Create a list of dates so you can ensure you have data for each supplier, make this window as small as possible.

Next I converted that date into a text string following this format: dd-mm-yyyy
And combine that text to the FolderRoot Parameter string, to generate a full path

Get the files from those folders

Extracted that data and updated the tables

Group on Vendor and keep the last record in that table


Combined the tables

Just add your root folder path to the FolderRoot parameter and select that value, to test it.
Here’s your sample file. eDNA - conditional append.pbix (22.9 KB)

I hope this is helpful


@Melissa Thanks for reply, I am working on this solution with my actual data, it looks workable, will let you know once I am done

Thank you very much :blush:


getting an error specifically when the source file is .xlsb type. error screen shot attached

i tried importing that file separately in powerbi & it works well

Please help. I am not getting this error