How to append records based on conditional criteria

Power Query
1

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

2

dummydata.zip (32.6 KB)

3

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.

image
image925×289 12 KB

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

image
image1337×359 23 KB

Get the files from those folders

image
image742×131 9.68 KB

Extracted that data and updated the tables

image
image1106×175 17.6 KB

Group on Vendor and keep the last record in that table

image

Combined the tables

image
image1480×447 45.2 KB

.
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

2 Likes
4

@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:

5

@Melissa

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

error
error1920×1080 153 KB