Streamline Your Product Management with Bilty Format in Excel for Product Management

Bilty format in excel for Product Management

in this video we are going to learn how to transform error and big Excel file like this into a beautiful looking Excel file like this in a single click welcome back if you are a person who uses Excel a lot and plays with data a lot you do same set of tasks or maybe you pull in from a larger report and you have to present it to some folks and that you do in a daily basis for example in zax girls what we do that we pull out the product details from the website and which gives us a raw data so let's say we have the file so I have downloaded it here and let me open that file so if I try to just expand it so you would see like everything is there so I but I don't need all these things right maybe I want to put certain kind of vlookups or something on top of it right so what do I do is very simple so this is basically my uh I'll not save it so this is basically my source file let's make a new folder called subscribe why not so and I will put this file there so it will be easier for us so this is the file that I have right so I'll create a new Excel sheet so I'll open Excel and I'll create a new Excel sheet okay and I'll save it inside the Subscribe folder and the name I would give it as let's say stock report Z means it's actually girls underscore stock underscore report now what I will go I'll simply go to data and here if you see get data power query this is where you want to go select here you what you will see like all kind of sources that you can you can have all data services you can have XML Json files right in the text or CSP format so I'll browse it and I'll select this file and get data then next connecting to the database now here if you see it has pulled the data this is one two three it means it is a number is ABC means it is a text type automatically it is saying all these things so we'll go to transform data now what we will do we don't want to show every columns right so how do you remove columns right so so we'll simply go and we'll choose columns and choose columns so I don't need all these columns right all I need is my excuse name talk in stock low stock amount regular price sale price then that's it I just need all these things right and I pressed ok so if you see now we have all the lists so here if you see this part is blank why is it blank because these are my variations like this is the product and these are my small medium large XL double XL whatever sizes that we have they would this would also have the same skews so if you see whatever steps I am applying I am doing here all things will be added as a steps here source is the first step that we did we selected from a source so this was the source then automatically Excel added these two tips then I chose few columns so if I want to you know modify that so I can go here I can add a few more I need this parent okay so this is what I need I selected okay this parent I want to move it to here so the moment I move it so here also one reordered step got added and I want to merge these two things right click merge columns okay separator is nothing I don't want any separator new columns name merged okay fine so I'll just say okay the moment I do that so here is a new column I rename it as Q so if the moment that I added I renamed it so here also it got added one one extra step attribute to one name I don't need this I can simply right click and remove columns also here if you see remove columns so it will add one step here let's say I want this attribute to be here now once I am happy with everything I can press close and load data okay now we have the data in a cleaner format let's say I don't need this large ml this line I don't need so I can go back to the data and I'll go to again here and launch power query editor now here what I can do I can select here at all I just deselect these two lines so now I'll close and load data so automatically it came okay let's say tomorrow or let's say I modified some records in my in my database so let's say for this product not your baby product I added few stocks first small is let's say 20 per medium also 20 19 quantity first large text cell 10 quantities double XL five quantities save it download the next report so the file got downloaded here what I have to do I can open this it go to the data okay okay launch power creator what I can do I can duplicate this okay or let's say I made it as after talk update okay I'll go to the source now I select the source and select it and select the second record so I'll get data then okay now simply I'll go to in the last step then again I close and load data once I close and load data as it is a new query it will create a new tab in the Excel so here it created a new tab let's say let me put the filter here sorry so if you see there is some blank records are here right which I don't need so I can in the power query I can remove this thing so how can I do in the click on this button and launch power query here what I will do select remove empty then close and load so I also not see any blank records correct so let's I put this queue only then I would get the new stocks that I got in a movement rate I don't have to do the whole uh Delete and all these things I don't have to do right just simply in one click I got my data that's it see you in the next one thank you