automating repetitive task in power query editor to clean up multi-files

[Anonymous]

hi,
i'm still developing a power bi dashboard/reporting app for a shared services co to show a consolidated dashboard/report for their 50+ clients. the datasource are ms excel files (generated by an accounting cloud app every month) for the 50+ clients. the files are loaded to a sharepoint folder as a central data repository. newer/updated files r regularly loaded here too.

currently, i use power bi's power query tool to get the individual client's files & 'clean' them individually. so, i do it repeatedly 50+ times every month. this is because i need to also add an organization identifier (org_id) for each of the clients for future filtering needs. subsequently, they are merged through the power query tool to create a dataset. later.

is there a way to avoid the repetitive routine by means of at least automating thru power query the cleaning the data part?

tks, -nik

[v-shex-msft · Accepted solution]

HI @Anonymous,

Any specific fields existed in your source data that map with each user name/id? Did these files named with particular regular that you can extract them to map with username?
If this is a case, you can create a dictionary table to mapping these fields values to user_id which you used to filter on records.
Then you can add a column to your table to use current fields value to lookup the dictionary table get correspond user_id.

Regards,
Xiaoxin Sheng

[Anonymous]

thanks for your reply, @v-shex-msft.

actually, there's no specific field in the source data to map each organization to the dictionary table. thus, i have to manually insert it via calculated column - individually to each file that's generated from the accounting cloud app.

i may have a solution to it by using power query to add a column with the column name based on each xlsx file name or a sheet in the the individual files. this probably can b done after i have cleaned up (removing columns/rows, trimming ) the data. can u / anyone kindly guide me to get that? 

tks, -nik-

[Anonymous]

hi all,

for info, i have managed to do the batch processing for multiplefiles (generated from an accounting cloud app & subsequently stored in a sharepoint folder). mostly, it's done through power bi's power query tool.

(instead of using calculated columns) i have named each worksheet in the ms excel files (those generated from the accounting cloud app) with the org_id  corresponding to their organization. that wil b used to map the contents (e.g. profit & loss / balance sheet) for their respective organizations. this is done before the files r bought into power bi through its power query tool to process them as batch & subsequently appended together (e.g. 50 profit & loss files into 1 profit & loss table). if there are any updates / corrections to the datasource (xlsx files generated from the accounting cloud app & stored in a sharepoint folder), i just need to do a refresh thru the power query tool. similarly, new files (in same configurations as others) will also b appended to the previous files using the power query tool.  

these are my references:

a. batch processing multiple files
    https://tinyurl.com/y85mswje

b. connecting sharepoint folder
    https://youtu.be/XuLnSYjmsJo 

tks & krgds, -nik

[FrankAT]

Hi @Anonymous,

yes it is, but you must give us some sample data.

Regards FrankAT

[Anonymous]

hi @FrankAT ,

i wish i can but they are large, sensitive (financial) data for a shared service co (who has 50+ clients). i'm also sori that i don't hv the oppty to create sample fm those data. anyway, if anyone has any generic (financial) data fm multiple different accounts to simulate similar requirement to mine as mentioned in my earlier email, tht may help too. sori again.

krgds, -nik 

[parry2k]

@Anonymous does the file format is same across all the files and the same transformation steps are applied?

[Anonymous]

yes, they are.

tks @parry2k .

krgds, -nik