Forum Discussion
Extracting data from same Excel template files
Hi everyone,
I am struggling with this for past day and can't figure out a way to figure it.
Since the data I work with is rather confidental, I try to explain it through the image below.
What I have is something similar. Now, I would like to import all files from the folder with the same template (approx 200) and all the others that are going to be additionally added, into my Power BI project.
First green rectangle shows the order number, area and author.
Next to it (not indicated on the picture), between first and second rectangle is Order in and Order out dates (Columns 7 and 8) which I would like to simply move to the Column 1 and overwrite the existing data that is now there and I don't need it.
Second, lower, rectangle is showing different values with true and false. (Column 1 are the titles, and the values in TRUE/FALSE are part of Column 2)
Finally, there is one more piece of data whose title is ''Total hours'' and is also in Column 1 but it's numerical value is located in Column 7.
Rather complicated explanation but I am not sure how to do it better. Any inputs are appreciated since I would be easily able to figure out a way around if there was some video or article on this topic. I usually work with flat excel tables or source my data from DB.
I am thankful for any advices.
Best,
Filip
Anonymous then what you should do is get data from a folder and not an individual file in your getdata method, apply all those steps once you have done a combine (as long as they all the same, it will copy the first file format)
that way you can apply it to multiple files or if you dont want to combine them, then i suggest creating a source parameter that you can create on one and copy and paste in all, or a function
combine : https://www.youtube.com/watch?v=RAmSdxt863s
functions/parameters: https://www.youtube.com/watch?v=GgwXt4LVmsU
6 Replies
Anonymous its a bit difficult to help without access to date, however this video is quite useful in understanding how to move complex excel data into a userable format, at least here you will learn some strategies
Dear vanessafvg,
Many thanks for your quick reply. The video shows me something which I already did and is only on an example of one file that needs adjustment.
I would like to highlight something that I obviously did not in my original post and that is that I managed to clean one file through Query editor in Power BI exactly as I want it. As well, I was able to Pivot it and changed it from the bottom-up and transferred these items as my column names.
However, I still have 188 files left with this type of sheet. However, each file has 6 sheets so I would have to do same steps as I did for the one I have now for the rest and that makes it hours or even days of work. I think it took me around 15-20 steps in Query editor to clean my data on the sheet I use only on one file...Anonymous then what you should do is get data from a folder and not an individual file in your getdata method, apply all those steps once you have done a combine (as long as they all the same, it will copy the first file format)
that way you can apply it to multiple files or if you dont want to combine them, then i suggest creating a source parameter that you can create on one and copy and paste in all, or a function
combine : https://www.youtube.com/watch?v=RAmSdxt863s
functions/parameters: https://www.youtube.com/watch?v=GgwXt4LVmsU