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!
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.
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. 😍😍😍
Great content. Is there a way to do the same thing with a folder full of text files? They import as binary in the Content column, but I can't figure out how to convert that to a table
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 ❤!
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.
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.
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?
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. 😁
@@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.
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.
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.
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 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 ?
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 ;)
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 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 _
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 ❤
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 :)
I've just discovered this channel, this dude is an amazing teacher.
My jaw just dropped. What an amazing lesson. Thank you!
You deserve every penny!
Thank you so much ❤️
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.
You're one of the best on youtube for this subject. Your videos are short but so powerful. Thank you so much.
Mind blowing stuff!!!
I was looking for the files to practice - I found them in your blog, Thanks Chandeep.
Chandeep, you are a genius with PQ, thanks!
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. 😍😍😍
That’s great content. You’re a great Teacher.
I’m actually doing something along this but as per usual Chandeeps ingenuity has no limit
Bravo Mr. Goodly Chhabra!
Absolute GENIUS!!!!!! I learned MANY new things in this video. Great job.
Great course! This was smooth. Do more like these real-time problems =)
Marvelously clear explanation! thanks Chandeep
Really you are Awesome to teach us in so easy manner..
Terima kasih.
Thanks a lot!
You sir, are brilliant! Thanks for sharing your knowledge is a simple to understand method. Keep up the great work.
Amazing!!!!! Greetings from Mexico.
Incredible. You're awesome. This will make me go back and simplify everything I've done :D
Thanks
Thanks a ton Mariusz!
Master class. Are you mvp now?
I voted for you.
Terrific tips here! Thank you!
Amazing tutorial. I have learned so much! Thanks Goodly 🙏
Glad it was helpful Alex :)
Thank you Goodly for your knowledge sharing. Really worth a ton..
Just awesome , Love your Power Query course and your studies!
Thank you for the outstanding tutorials! They have been immensely beneficial.
Great to hear :)
Great content. Is there a way to do the same thing with a folder full of text files? They import as binary in the Content column, but I can't figure out how to convert that to a table
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 ❤!
😊😊😊😊
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.
Well, that was just beautiful. Cannot wait to use this method!
Token of Gratitude!
Thank you so much
Wow! Nice one! How do you make annotations at the same time you are recording ?
Great help vids, youre awesome, I see you changed the wording in your courses shoutout, i half expected to sing along again
Nice Video Chandeep.👍
Perfect! Thank you for sharing.
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.
again!! amazing case study video !!! can we get the same videos of case studies in the future also?
Thank you very much for this informative video
Singh you smash it every time bro 👊🏽⚡️
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.,
Toooooooooooo Gooooooooooood sirji
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/
Hi Chandeep! Can we perform a similar method when we import csv files?
awesome as always!
Amazing 🤩 thank you!!!
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.
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?
WonderFul.. 👍
thank you so much was handling a file per file
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. 😁
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🙂.
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.
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.
Awesome👌👌👏👏
Awesome👍
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.
Top de mais!!!
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 ;)
Great Sir
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
Super!
what do you mean by Pivoated?
Fantastic
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?
Hi!
Could you update this video with multiple sheets in each file.
Goodly bhai make a video on IRR MONTH WISE PLEASE
this guy has a special place in Heaven!
haha Thanks john :)
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!
Yar bhai kahan se sikha inta
time pass karte karte agaya bro!
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!
Chandeep you know how to teach Man
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. :(
Talk slowly bro😢
we are not interested to learn M language
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