Forum Discussion
Excel sheets from sharepoint
Hello vpanchu
i know that if you are reading from so many files and then want to add columns in your productive systems, this will be really ugly, because however to update your preview PQ has to start getting the data again. To it would make sense so reduce the amount of data when your are developing by using two different parameters so you can work on a small amount of data and when it's finished you can use all your data. This is how I did always when working with a lot of files
BR
Jimmy
Hello vpanchu
about challenge 1: Do you just read the data and combine or are you also transforming it? Are you accessing the file once or multiple times? If you are accessing it multiple times, you can think of using a Buffer-function to limit access. Normaly just reading a file and combining them should not take too long. How long it exactly takes? How big is each file?
challenge 2: You are talking about column names right? And you are using a template, meaning every Excel-file should be exactly the same. If so, you could first read the folder, then access every column headers and check if they are the same as your template (you could hardcode a list with your column headers and use this list to make the checks. So you would get a list of files and indicator if the column names are ok
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi Jimmy801
Thank you for the response.
I have answered your questions below
about challenge 1: Do you just read the data and combine or are you also transforming it? Are you accessing the file once or multiple times? If you are accessing it multiple times, you can think of using a Buffer-function to limit access. Normaly just reading a file and combining them should not take too long. How long it exactly takes? How big is each file?
I Read excel sheets and do some transformations (removing and renaming rows and columns) but nothing heavy as i am not able to its very slow after i load more than 10 excel from diffrent sharepoint folders and Append all to one , and later I uncheck the Enable load option in the seperate files and keep only the appended file in power query for viz.
Data Source details :
Sharepoint folder , every country has a folder and 5 to 20 excel sheets, 40 columns in a excel sheet , not morethat 1000 to 2000 rows per excel sheet as of today , it may be populated with more 2000 every year.
challenge 2: You are talking about column names right? And you are using a template, meaning every Excel-file should be exactly the same. If so, you could first read the folder, then access every column headers and check if they are the same as your template (you could hardcode a list with your column headers and use this list to make the checks. So you would get a list of files and indicator if the column names are ok
Hence i do remove some columns and promote headers ect to set the fields correctly in Power BI as it has lot of unwanted rows aboe in the excel file, can i aply the above techniq in this case?
Any more suggetions for me ?
Regards
VP