Clean Up and Combine P&L Statements from Multiple Excel Files || Power Query Case Study
Vložit
- čas přidán 21. 07. 2023
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, we will walk you through a practical case study on how to clean up and merge Profit and Loss (P&L) data from multiple Excel files automatically using Power Query.
We'll organise, transform, and merge multiple P&L worksheets into one consolidated report using various functions and techniques in Power Query.
#PowerQueryCaseStudy #ExcelAutomation #DataAnalysis
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/clean-up-and-mer...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers! - Věda a technologie
You're one of the best on youtube for this subject. Your videos are short but so powerful. Thank you so much.
4 minutes 30 seconds into the video. That's when my jaw hit the floor. 4 minutes and 30 seconds. I had to stop the video at that point and give my brain time to digest what it had just received. To work it's way through the myriad of hours I've spent trying to find a way to do exactly that. You are amongst the very few content creators that can do that to me. My oh my!
Thanks. There is more to come :)
Ingenious as your number selection was I found ;
List.Select(
List.Transform( Table.ColumnNames( Custom1 ), (B)=> try Number.From(B) otherwise "A" ),
(C)=> C "A") a bit simpler.
My jaw just dropped. What an amazing lesson. Thank you!
I've just discovered this channel, this dude is an amazing teacher.
Absolute GENIUS!!!!!! I learned MANY new things in this video. Great job.
Amazing!!!!! Greetings from Mexico.
That’s great content. You’re a great Teacher.
Great course! This was smooth. Do more like these real-time problems =)
I’m actually doing something along this but as per usual Chandeeps ingenuity has no limit
Just awesome , Love your Power Query course and your studies!
Mind blowing stuff!!!
I was looking for the files to practice - I found them in your blog, Thanks Chandeep.
Terrific tips here! Thank you!
Well, that was just beautiful. Cannot wait to use this method!
You sir, are brilliant! Thanks for sharing your knowledge is a simple to understand method. Keep up the great work.
Chandeep, You are genius and sharing such knowledge is truly noble deed. Great appreciation from bottom of my ❤. I have learnt a lot and always wait for weekend to watch your videos. 😍😍😍
Chandeep, you are a genius with PQ, thanks!
Perfect! Thank you for sharing.
Really you are Awesome to teach us in so easy manner..
Thank you Goodly for your knowledge sharing. Really worth a ton..
Marvelously clear explanation! thanks Chandeep
Bravo Mr. Goodly Chhabra!
awesome as always!
WonderFul.. 👍
Amazing 🤩 thank you!!!
Incredible. You're awesome. This will make me go back and simplify everything I've done :D
You deserve every penny!
Thank you so much ❤️
Thank you very much for this informative video
Awesome👌👌👏👏
Toooooooooooo Gooooooooooood sirji
Nice Video Chandeep.👍
thank you so much was handling a file per file
Awesome👍
Thank you for the outstanding tutorials! They have been immensely beneficial.
Great to hear :)
Singh you smash it every time bro 👊🏽⚡️
Super!
Great Sir
Amazing tutorial. I have learned so much! Thanks Goodly 🙏
Glad it was helpful Alex :)
Hi Goodly! Haven't faced such task yet but still looks amazing how much could be accomplished with Power Query. Recently I managed to adapt one of your latest techniques for automatically detecting Headers no matter at which row they start (you showed something similar in this video, as well) and it works like a charm! At this point my Power Query would stop working only if someone manages to change a name of a column which we need to keep as a part of the final result (and I have added Proper case step so upper or lowercase letters in columns' names won't stop Power Query. As long as we have a key word in source's name, correct sheet's name and all our crucial columns' names intact everything is fine when I handover such automated task to someone else who just have to update the sources, refresh, save and send latest results. And this saves so much time it is unreal but also prevents the chance for human error. Thank you again for all your time and efforts you have put in this cause. It really makes a difference ❤!
😊😊😊😊
Great help vids, youre awesome, I see you changed the wording in your courses shoutout, i half expected to sing along again
Fantastic
Goodly, great video as always! One thing. For some reason when I tried to edit any of the M Code, the Intelisense would not work! I was able to use it in a new blank file and even older files without a problem. I found that the file's language was English(India) and changed it to English(US) but that didn't help either. I checked all the relevant settings but couldn't find a solution. I am using the Insider Beta edition, but I doubt that's the issue. Any idea of what the problem might be?
again!! amazing case study video !!! can we get the same videos of case studies in the future also?
Another case study! I really enjoy trying these out on my own and then looking at your method.
Please keep uploading these!
Thanks James! There is more to come :)
I wonder why Power Query isn't used in my company. We are about 150 and I am the only one having knowledge in M language. Such a good tool.
@@silverfunnel6819 the same situation in our company. VBA is maximum at couple people. Moreover they often use xls instead of xls* format.🤔 And i'm always resaving them into new format!
@@silverfunnel6819 Often it is lack of knowledge. Most people don't venture beyond what they were first shown. You need to be the one to show them the light. If they continue to stay in the darkness that is their failing.
@@pabeader1941 You hit the bull's eye🙂.
Fantastic video, as always! Would you be able to create a similar video covering a case where you deal with tables with variable column names. E.g. when exporting pdf file with tables into PQ which in PQ looks jumbled up, i.e. columns and rows misaligned, column headers appearing in different rows and with variable empty cells in-between.
Master class. Are you mvp now?
I voted for you.
Wow! Nice one! How do you make annotations at the same time you are recording ?
HI Goodly, Your video are awesome, Details, and advanced, Easy to follow and quick to learn, I am beginner and learnt most stuff watching your video, I work in Readymix Concrete industry and so i have different location instead of Stores, The first line item of income statement of my industry is the sales volume quantity in M3(metric Cube), so all the rest of Main and Subline line items are divided by Sales volume to reach the unit per M3 of every item, Could you please provide me measure to divided first line item sales volume with rest of the Item. This is the biggest hurdle i am facing as beginner and also share like of video showing tips to write a measure.,
Hi, Chandeep. I can see this solving many issues I currently have. Is it possible for you to post/paste the Advanced Editor (M code) for both the function and the query. I'm one who needs to see the code and interpret the whole. I love your videos, they help me so much! I'm saving this video in my library so I can go through, step-by-step, to solve and develop muscle memory.
UPDATE: I was reading the comments below and see you have sample files in your Blog. I'll go there to get the M Code.
I have created something similar recently using the same technique. I referred a bunch of ur videos for that and my solution was quite similar.
All credit to you and ur videos.
Thanks
Thanks a ton Mariusz!
Hi Chandeep! Can we perform a similar method when we import csv files?
First a disclaimer, I am a novice so my questions will show my lack of expertise. Why did you take the route of cleaning one file and making the code a function to then use on all the files, instead of loading all the files from a folder, select combine and transform and clean the data using the techniques in your video. Wouldn't that apply all the steps to all the files in the folder, just like the function solution in the video does? If the answer is "yes", what are the benefits of going the function route? Or is it a personal preference? Also, I would want to keep the store ID somewhere in my reporting. I think it is on the first line or two of the source data. Any ideas how to keep that in the solution? Thank you so much. I really am enjoying your videos, have recommended you to several friends, and will likely sign up for your class in the near future.
On our production we had such file. And i was ready to convert this to normal view, but i've asked could we enter data in a bit another format and i've got the answer YES.
So, i just create normal table where now they enter data. 😁
Hi Goodly! I have used your video to read a lot of PDF-Files. The Problem is, that in the PDF‘s there are pages and tables. In the function I have filtered rows only for pages. After expanding the pages I have made the necessary steps to get the data I need. So far so good - but how can I use the function for the other files, if every files includes pages and tables? I hope you can help me or better said, you have time for answering my question. Thank you in advance.
Terima kasih.
Thanks a lot!
Hi, you can add a column with the store name from the first row of the firts column, instead of deducing it from the file name. There is an easy way.
Goodly bhai make a video on IRR MONTH WISE PLEASE
Excellent video and I learnt lots of new techniques.Can you please provide files too?
Thanks Bhavik. You can get the file here - goodly.co.in/clean-up-and-merge-from-multiple-excel-files-automatically-power-query-case-study/
what do you mean by Pivoated?
i have same problem but in deferent formant , how you can help me
I can't seem to get this to work with a folder full of csv files. Any advice?
great work, but my P& L tables are different, and that answer , not work with it.
So, can I share with u my data ? To explain for us new things
Token of Gratitude!
Thank you so much
Hi!
Could you update this video with multiple sheets in each file.
Great video Chandeep as usual. At 15min02 you commented several line with several // at once ? Uselly i wrap them between /* and */ . What was the keybord shortcut used to comment several line with // ? Looks pretty for development purpose ;) thanks in advance ;)
Ctrl /
@@GoodlyChandeep thanks a lot ;) will be usefull.
@@GoodlyChandeep I tried "Ctrl" button simultaneously with the "/" button I tried a bunch of potential key combination but it did not work neither. Does it require q specific add-in ?
NOICE!!!!
For some reason, ctrl + / does not work on my computers… but I found another shortcut in microsoft documentation that works ;) ctrl + k + c to comment and ctrl + k + u to remove the comment ;)
Chandeep you know how to teach Man
this guy has a special place in Heaven!
haha Thanks john :)
I prefer to generate values I need to keep and then filter by them like so:
let
Source = { 1, 2, 3, 2023, "2022", 2020, "a", 5.5, 6, "b", 8, 8.5, "c", "null", null },
Converted = List.Transform ( Source, each try Number.From ( _ ) otherwise null ),
YearsList = List.Generate ( () => 2019, each _
Neat!
Followed this all the way through to end up with " An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "InputTable" to type Table.
Details:
Value=InputTable
Type=[Type]"
I figured it out. I've found this instruction immensely helpful!
All is ok, but rocket animation is really bad idea...
Greetings from Ukraine!
Oh crap.. I thought people are gonna like it :(
@@GoodlyChandeep you've used not proper type of rocket. :(
we are not interested to learn M language
Talk slowly bro😢
Hey, again a very interesting case study with unstructured file. The tips of Table.Skip function in the beginning is priceless thank you !
Another thing, interesting way to use [HasError] in the record.
Maybe another way with the syntax « try Number.From(_) otherwise null » and use the function List.RemoveNulls. Same result at the end but just different way 😬
Thanks a lot for all your videos ❤
I
Another possibility → try Number.From(_) is number otherwise false