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