Going to tackle is we're going to tackle getting the product detail sheet from each of these files so we have accessories product detail bikes and clothing product detail so let's head.
Over to powerbi desktop and we're going to go get data more and I just go to file and folder hit connect we're going to point to our.
Folder learn powerbi hit okay now it gives me this option I have a few options here I can say combine and edit combine and load or just click load which will load the table as is if.
That's what you're looking for or edit which would lend uh land you in the editing pane with these files so for now I'm going to say combine and edit and it brings me this screen which.
Files and for our scenario we're going to just select an example file and say product detail so that's the element I want to be extracted from each file now when I hit okay it's going to do all the.
Heavy lifting for me so we're back and notice my query is ready it went ahead and grabbed the you know the the uh the product detail sheet from e each of those and this is the end.
Results so all the hard work is done but you notice that man it created quite a slew of queries but that's okay um now it keeps the source name the file name here you.
Can delete it if you don't want it but that's our table we can hit just hit close and apply and we're going to end up with our Barbi product table let me rename it really.
Quick the first scenario is very clean but let's imagine a scenario where we need to combine the budget summary file so if you go to the file so you can see budget.
Posts Related:
- Automatically scale with hosted RPA bots in Power Automate
- How To Create BMR Calculator Using HTML, CSS & JavaScript Part 2 BlogSpot
- Python Part 14c - Working with dates and times Office
Summary it is slightly more cluttered we
Need more Transformations and this time we need to grab the budget summary file from each of these Excel files let's see how we can do that we're going to go.Back to Barbi desktop again get data more file fold folder hit okay same folder as before and we're going to click combine and.
Edit again it asks me to choose the element to be extracted and this time I'm looking for Budget summary so I'll select that hit okay and it's done so I'll say.
Budget summary great but unlike product which was very clean and it did everything right and this stable was ready to load if you look at budget summary it's full of Gunk right I mean.
Gosh all those header rows and all those files they've been combined so we don't we don't quite want that so at this point you have two options one option is that you do your Cleanup in the combined.
F F but if you're willing to dive into the inner of uh the structure that was the scaffolding that was created for you there is an easier way which is which is actually going to work better which is.
You instruct powerbi or teach powerbi how to process each file before it's combined in this manner so what's happening here is that it's the key uh step here is where it invokes this.
Custom function right and it's invoking this this function right here this one now if I were to go over and look at that function and click Advanced editor it's going to give me a warning and what.
This warning is saying is that this function is tied to this query like SII twins whatever change you make to this one is going to be reflected in this.
Function however if you were to ignore
This warning then that link would be broken uh right so that's how Barbi set up so again so we can use this to our advantage we can go to this sample file.Sample transformation and build a set of transformation which we want to be applied on each tab each budget summary tab before it's pulled in so let's step.
Through those really quick remove top rows similar to what we have seen earlier and we'll say does not contain great use First St.
Headers remove all right so we're done with these and all of these changes are reflected in our file so if we go to our budget.
Summary table there's an error but this is pretty meaningless it's only happening on the change types app uh change type step we can easily fix that if you go back to our custom function.
That's where the custom function is invoked and then if you look at when the results are expanded then you see that it's a very clean table because all the steps that we appli to the sample file.
Are now being invoked using the function on each of the uh of the uh sheets here so that's it I can can of ignore the change type because I've already handled change type in the function uh so that's.
It so either way if you have a clean way um clean file combining that is easy and in that case you can pretty much ignore all of these underlying functions but if you have a complex scenario and if you.
Combine it and it doesn't yield the results that you need then you can go in to the sample file and notice you can tell that by the icon here so you don't want to be editing the function uh the.
Function function here with the FX icon in fact as you saw earlier it's going to give you a warning if you try to do that so the way to do it is whatever changes transformation you apply to this is.
Going to be applied mirrored in the function and in turn is going to be applied to each of the elements which are being extracted and combined so another question that I've.
Been asked is how do I combine files but retain the file name as you notice here in both of these the the file name is retained so it has Source name and the budget summary has uh the source name as.
