Clean Up and Combine P&L Statements from Multiple Excel Files || Power Query Case Study

Подписаться 89 тыс.

Просмотров 20 тыс.

50% 1

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!

Наука

Опубликовано:

21 июл 2023

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 99

@pabeader1941 11 месяцев назад

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!

@GoodlyChandeep 11 месяцев назад

Thanks. There is more to come :)

@williamarthur4801 11 месяцев назад

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.

@omermirza5994 2 месяца назад

Incredible. You're awesome. This will make me go back and simplify everything I've done :D

@hapemokenela7388 10 месяцев назад

I've just discovered this channel, this dude is an amazing teacher.

@mnowako 11 месяцев назад

My jaw just dropped. What an amazing lesson. Thank you!

@emilmubarakshin49 11 месяцев назад

I’m actually doing something along this but as per usual Chandeeps ingenuity has no limit

@krishanphophalia9303 11 месяцев назад

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. 😍😍😍

@OdelLeal 11 месяцев назад

That’s great content. You’re a great Teacher.

@syrophenikan 11 месяцев назад

Absolute GENIUS!!!!!! I learned MANY new things in this video. Great job.

@Wid76 11 месяцев назад

Just awesome , Love your Power Query course and your studies!

@mirrrvelll5164 11 месяцев назад

Great course! This was smooth. Do more like these real-time problems =)

@imadnb3579 11 месяцев назад

Mind blowing stuff!!! I was looking for the files to practice - I found them in your blog, Thanks Chandeep.

@jimfitch 11 месяцев назад

Terrific tips here! Thank you!

@paser2 11 месяцев назад

You sir, are brilliant! Thanks for sharing your knowledge is a simple to understand method. Keep up the great work.

@BrainyBrunetteBarbie 11 месяцев назад

Well, that was just beautiful. Cannot wait to use this method!

@chrism9037 11 месяцев назад

Chandeep, you are a genius with PQ, thanks!

@KuldeepSingh-nq1vi 11 месяцев назад

Really you are Awesome to teach us in so easy manner..

@GrainneDuggan_Excel 11 месяцев назад

Marvelously clear explanation! thanks Chandeep

@hariprasad289 11 месяцев назад

Thank you Goodly for your knowledge sharing. Really worth a ton..

@pabeader1941 11 месяцев назад

You deserve every penny!

@GoodlyChandeep 11 месяцев назад

Thank you so much ❤️

@raimundojs9547 11 месяцев назад

Perfect! Thank you for sharing.

@Piyush.A 11 месяцев назад

Bravo Mr. Goodly Chhabra!

@user-hk1uu7nc9h 4 месяца назад

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.

@alinelviscitalungu4132 10 месяцев назад

Thank you for the outstanding tutorials! They have been immensely beneficial.

@GoodlyChandeep 10 месяцев назад

Great to hear :)

@ravchana2393 10 месяцев назад

Singh you smash it every time bro 👊🏽⚡️

@vadnerepravin 11 месяцев назад

Thank you very much for this informative video

@alexrosen8762 10 месяцев назад

Amazing tutorial. I have learned so much! Thanks Goodly 🙏

@GoodlyChandeep 10 месяцев назад

Glad it was helpful Alex :)

@sanumpzha 11 месяцев назад

Nice Video Chandeep.👍

@leroymarcelswartz8693 11 месяцев назад

Great help vids, youre awesome, I see you changed the wording in your courses shoutout, i half expected to sing along again

@tunaikinyanguk5493 11 месяцев назад

awesome as always!

@karinamatvejeva3164 11 месяцев назад

Amazing 🤩 thank you!!!

@brightonalbert3237 4 месяца назад

thank you so much was handling a file per file

@siddheshamrutkar8684 11 месяцев назад

WonderFul.. 👍

@mohammedabdulwahab4152 6 месяцев назад

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.,

@balajisurvase142 11 месяцев назад

again!! amazing case study video !!! can we get the same videos of case studies in the future also?

@mohitchaturvedi8931 11 месяцев назад

Master class. Are you mvp now? I voted for you.

@Chillman666 11 месяцев назад

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 ❤!

@rexnguyen6247 9 месяцев назад

😊😊😊😊

@brij26579 11 месяцев назад

Awesome👌👌👏👏

@ssananda 11 месяцев назад

Toooooooooooo Gooooooooooood sirji

@mdm3770 3 месяца назад

Wow! Nice one! How do you make annotations at the same time you are recording ?

@kebincui 11 месяцев назад

Awesome👍

@tnssajivasudevan1601 11 месяцев назад

Great Sir

@jamesstramer5186 11 месяцев назад

Another case study! I really enjoy trying these out on my own and then looking at your method. Please keep uploading these!

@GoodlyChandeep 11 месяцев назад

Thanks James! There is more to come :)

@silverfunnel6819 11 месяцев назад

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.

@Rice0987 11 месяцев назад

@@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!

@pabeader1941 11 месяцев назад

@@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.

@silverfunnel6819 11 месяцев назад

@@pabeader1941 You hit the bull's eye🙂.

@abdulhaseeb8027 11 месяцев назад

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.

@abdulhaseeb8027 11 месяцев назад

All credit to you and ur videos.

@geovanrocha2822 4 месяца назад

Super!

@PedroCabraldaCamara 11 месяцев назад

Fantastic

@RobertoPoehlmann 11 месяцев назад

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.

@Rice0987 11 месяцев назад

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. 😁

@lynxwomancat 11 месяцев назад

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.

@Bhavik_Khatri 11 месяцев назад

Excellent video and I learnt lots of new techniques.Can you please provide files too?

@GoodlyChandeep 11 месяцев назад

Thanks Bhavik. You can get the file here - goodly.co.in/clean-up-and-merge-from-multiple-excel-files-automatically-power-query-case-study/

@jerrydellasala7643 11 месяцев назад

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?

@light_gaming7235 11 месяцев назад

Goodly bhai make a video on IRR MONTH WISE PLEASE

@saviofarrar8282 10 месяцев назад

Hi Chandeep! Can we perform a similar method when we import csv files?

@mnowako 11 месяцев назад

Thanks

@GoodlyChandeep 11 месяцев назад

Thanks a ton Mariusz!

@rifkagumay 5 месяцев назад

Terima kasih.

@GoodlyChandeep 5 месяцев назад

Thanks a lot!

@alexanderbitschi7623 10 месяцев назад

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.

@eslamfahmy87 11 месяцев назад

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

@ankursharma6157 10 месяцев назад

Token of Gratitude!

@GoodlyChandeep 10 месяцев назад

Thank you so much

@Deoreth 11 месяцев назад

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 _

@GoodlyChandeep 11 месяцев назад

Neat!

@eisaal-binali4968 13 дней назад

i have same problem but in deferent formant , how you can help me

@robrayborn1349 4 месяца назад

I can't seem to get this to work with a folder full of csv files. Any advice?

@aftabsiddique3650 9 месяцев назад

Hi! Could you update this video with multiple sheets in each file.

@minadev1 3 месяца назад

what do you mean by Pivoated?

@Anthony_Lecoq 11 месяцев назад

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 ;)

@GoodlyChandeep 11 месяцев назад

Ctrl /

@Anthony_Lecoq 11 месяцев назад

@@GoodlyChandeep thanks a lot ;) will be usefull.

@Anthony_Lecoq 11 месяцев назад

@@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 ?

@nunolourenco1532 11 месяцев назад

NOICE!!!!

@Anthony_Lecoq 11 месяцев назад

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 ;)

@johnveliku 11 месяцев назад

this guy has a special place in Heaven!

@GoodlyChandeep 11 месяцев назад

haha Thanks john :)

@Ratnakumarwrites 11 месяцев назад

Chandeep you know how to teach Man

@robrayborn1349 11 месяцев назад

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]"

@robrayborn1349 10 месяцев назад

I figured it out. I've found this instruction immensely helpful!

@Rice0987 11 месяцев назад

All is ok, but rocket animation is really bad idea... Greetings from Ukraine!

@GoodlyChandeep 11 месяцев назад

Oh crap.. I thought people are gonna like it :(

@Rice0987 11 месяцев назад

@@GoodlyChandeep you've used not proper type of rocket. :(

@user-wn9sj7io7c 8 месяцев назад

Talk slowly bro😢

@farooqtahir538 22 дня назад

we are not interested to learn M language

@baroute78160 11 месяцев назад

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 ❤

$@fracyoulongtime8123$