APQ04: Advanced Power Query Video #4 - M-CODE - Combine Inconsistent Tables
HTML-код
- Опубликовано: 2 авг 2024
- Chapters:
--------------------------------------------------------------------------------------
0:00 Intro
1:30 Combine Multiple Tables in one Workbook
5:24 Extract a Dynamic List of Column Headers
10:37 Dynamically Expand Columns
15:07 Closing
--------------------------------------------------------------------------------------
To watch the full playlist
• Advanced Power Query
--------------------------------------------------------------------------------------
To download the working files:
drive.google.com/drive/folder...
--------------------------------------------------------------------------------------
To subscribe:
/ @aaaexcel
--------------------------------------------------------------------------------------
To follow on Facebook
/ excelispower
--------------------------------------------------------------------------------------
To follow on LinkedIn:
/ amratef
You are sacrificing all the knowledgeable treasure of Excel on the world, in return you will get the love and respect of the world.
Thank you so much for your nice words, appreciate it
Another great lesson...for sure
thank you
You've explained it far more clearly than the tutorial I've got from Online Courses. Thank you for this awesome video.!!
happy that you find it useful, please subscribe to the English channel: youtube.com/@aaaexcelenglish
Thank you for the excellent material!
My pleasure
Another spectacular lesson. Thank you very much for spreading relevant knowledge.
Most welcome dear 🙏
Great video!!
Thanks!
Very nice tutorial 👍
Thank you 🙏
This channel is the best one I have seen in RUclips, Excellent content, Great work
Thank you so much 😀
Wonderful keep it up Thank you dear
Thank you too
Excellent video! Keep up the great work.
Thank you , happy you like it
Great Series! Keep Going!
Thanks, will do!
Awesome👍
Thank you 🙏
Thanks Amr for this wonderful video.
Glad you liked it! thank you
wonderful!.. such issues are usually faced in appending. this video is a complete solution to it.
Thank you, happy you find it useful 👌
Wonderful video, keep it up and thank you for sharing💌
Thank you for your comment, you’re welcome ☺️
It really helps me . JAZAKALLAH KHAIR. Thanks a lot.🧡🧡🧡
Jazak Allah Khair, happy that it was useful
Excellent. Keep it up👌👍
Thank you 🙏
Excellent video , thank you sir
Most welcome 🙏
I've learning M code by coding :D
Excellent
Thank you so much sir, You explain the List very very Awesome, its so much easy. Thanks for this video. One again sir Thanks.. ❤️❤️
You are most welcome
@@AAAExcel Hi, I do have a question.
why did you overcomplicated that?
Here is my code:
let
Source = Excel.Workbook(File.Contents("C:\Users
jpop\Downloads\Different_Column_Tables.xlsx"), true, true)[Data],
Custom1 = Table.Combine( Source )
in
Custom1
this way there is no need to grab column names separetly, and it will be done automaticly.
I tried this one, amazing, it’s working perfectly.. thank you 🙏
If you allow me, I will do a quick video to explain it
@@AAAExcel sure go ahead :)
to me thats common knowladge, or maybe im so deep in PQ that this is super obvious to me, I dont know. but anyway, sure make video out of it :)
If only I had some1 for video editing.
I do love making videos, i hate edditing videos.
Nice job explaining all the details; big help.
One issue I am having is when I do the Transform of the data file in addition to the the sheets of the source wokbook, I see a bunch of ExternalData tables which are marked "Hidden". This results in a bunch of undesired rows in my appended worksheet. Where did thes ExternalData sheets come from, and how can I get rid of them?
Thank you very much.
I should have mentioned that when I examined the source workbook, there are no hidden sheets there.
My guess is that you have named ranges, tables or print areas, on a separate note, please watch this video, it can achieve the same but much faster and more simple
ruclips.net/video/B3ecoWczI8Q/видео.htmlsi=3P_KaRc8WtVYXRSt
Dear Sir please make video on power BI in English Also...
It will come with the power pivot series. next video will come soon
What if i don't want any null value in new column?
what do you want instead? you can relpace null value with a defalut value if requried
يعطيك العافيه استاذ
أنا بشتغل محاسب تكاليف بمطعم و جردت اخر السنه و عملت تسوية جردية للمستودع عشان اثبت الفعلي عملت تسوية مدينه و تسويه دائنه
الفعلي طرحت منه المعياري و بعدها خرجت الموجب و أدخلت السالب ع المستودع عشان ما يطلع فرق و فعليا المستودع صار الفروقات فيه صفر لكن ع ميزان المراجعه حساب المخزون كبيرررر يعني ضعف بضاعه اخر المده شو السبب؟
اهلا بك .. معلش. ايه علاقة السؤال بموضوع الڤيديو؟ هل كنت شغال علي الملفات باستخدام باور كويري او حاجة زي كده؟
اه شغال بور كويري
How do I export data with file name using power query for text files.
You can go to data, get data, from file, from folder, then select the folder contains the text files and click ok
The power query will launch and you will a list of all files of the folder
@@AAAExcel My point was if I combine binaries (content column) in power query, I don’t want to lose the file name column. I want to extract file name together with content.Remember the source file is a text file or CSV.
Ok, then you can use add custom columns, in the new column use function “CSV.Document”, then remove all the columns except the file name and new custom column then expand