مقدمه
دوستان، موضوعی که می‌خواهم به آن بپردازم این است که ما معمولاً نمی‌خواهیم نیازهای واقعی‌مان را بشناسیم. این جمله در تمام جنبه‌های زندگی ما صادق است. در اینجا منظور من دانش کار با انواع داده‌هاست
این دانش به شدت برای همه کسانی که در شرکت‌های دولتی و خصوصی با خواندن، تجمیع، تمیز کردن، بصری کردن (visualize) و تفسیر داده‌ها سروکار دارند، ضروری است. اما تعداد افرادی که برای کسب این مهارت تلاش می‌کنند، بسیار کم است. ما با طیفی از افراد روبرو هستیم. در یک سمت این طیف، کسانی هستند که به هیچ وجه تغییر را نمی‌پذیرند. راستش را بخواهید، تغییر ترسناک است؛ زیرا به معنای پذیرش این است که تا به حال اشتباه کرده‌ایم.
دسته‌ای دیگر تغییر را می‌پذیرند، اما مسئولیت آن را بر عهده نمی‌گیرند. این افراد منتظرند تا دیگران، مانند واحد IT، گزارشات مورد نیاز را تهیه کرده و در دسترس کاربران قرار دهند یا اینکه فقط به نوشتن چند فرمول ساده در اکسل بسنده کنند تا بتوانند چهار عمل اصلی را انجام دهند.
گروه سوم افرادی هستند که با هر انگیزه‌ای به دنبال کسب و توسعه این مهارت هستند. این افراد با دستکاری‌هایی که روی خروجی گزارشات سیستم انجام می‌دهند، سعی می‌کنند آنها را قابل فهم‌تر و مفیدتر کنند و حتی کارکنان IT را تحت فشار قرار می‌دهند تا نیازهای کشف شده را برای بهبود سیستم برطرف کنند.گروه چهارم علاوه بر همه این‌ها، دلسوزانه - اگر نگویم پیغمبرانه - تلاش می‌کنند تا دانش خود را به دیگران منتقل کنند. این افراد گاهی در ذهنشان زمزمه می‌کنند: "کس ندارد ذوق مستی..."
حالا شاید صدای شما بلند شود و بگویید: "قرار بود درباره PDF صحبت کنی، چرا شعر تحویل من می‌دی؟" اما باید بگویم که پاور کوئری یک ابزار همه‌فن‌حریف است! یعنی چه؟ فرض کنید یک صفحه وب دارید که شامل جدولی است که برای شما مفید است. درست مانند همین جدول، شما یک جدول مشابه در اکسل دارید و همچنین جدولی دیگر در Access حالا انتظار دارید من همین جا تمامش کنم؟ نه! لطفاً کمی صبر کنید.
علاوه بر این‌ها، یک جدول دیگر به صورت CSV روی هارد دیسک شما ذخیره شده و از همه مهم‌تر، یک فایل PDF دارید! بله، درست شنیدید؛ یک فایل PDF که شامل جدولی مشابه سایر جداول است. پاور کوئری قابلیت خواندن همه این‌ها را دارد! شوخی نمی‌کنم؛ باور کنید!
علاوه بر این، پاور کوئری در یک محیط کاربرپسند نه تنها همه داده‌ها را زیر هم قرار می‌دهد (Append)، بلکه نتیجه را به صورت جدول معمولی یا PivotTable یا نمودار در اکسل نمایش می‌دهد. اگر هر یک از فایل‌هایی که ذکر کردم آپدیت شوند (مثلاً فایلی که تحت وب است)، شما با یک Refresh می‌توانید همه آنها را دوباره زیر هم بچینید.
شاید بپرسید: "چگونه می‌توانم پاور کوئری را تهیه کنم؟" خوشبختانه مایکروسافت از نسخه 2016 به بعد دسترسی به پاور کوئری را بسیار آسان کرده است. کافیست به سربرگData بالای صفحه نگاه کنید؛ گروه Get & TransformData به وضوح قابل مشاهده است.
ما قصد داریم تمام جداول موجود در فایل‌های PDF را زیر هم در یک فایل اکسل بیاوریم. پیشنهاد می‌کنم قبل از هر چیز به ضمیمه مقاله مراجعه کنید و دو فایل PDF را با دقت بررسی کنید. توجه داشته باشید که اطلاعات مشابه هستند، اما ترتیب ستون‌ها ممکن است متفاوت باشد. بنابراین لطفاً با دقت به هر شش جدول نگاه کنید و اختلافات آنها را کشف کنید. سپس برای همراهی با من یک پوشه بسازید و هر دو فایل PDF را در آن کپی کنید.
در اکسل، برای دسترسی به پوشه‌ای که حاوی فایل‌های PDF است و ارسال آنها به ادیتور پاور کوئری، مراحل زیر را دنبال کنید:
مسیر دسترسی:
از منوی Data به Get Data بروید.
سپس گزینه From File و بعد From Folder را انتخاب کنید.
توجه به توضیحات اکسل:
اکسل توضیحاتی درباره تب From Folder دارد. این توضیحات شامل نحوه انتقال داده‌های متا و لینک‌ها درباره فایل‌های موجود در پوشه است. بنابراین، انتظار نداشته باشید که پاور کوئری به طور خودکار فایل‌ها را باز کند.
انتقال به محیط پاور کوئری:
در پنجره بعدی، روی دکمه Transform Data کلیک کنید تا به محیط پاور کوئری بروید.
مشاهده ستون‌ها:
در سه ستون اول، نام فایل، پسوند و ستون Content را مشاهده می‌کنید. ستون Content شامل محتوای فایل‌هایی است که از پوشه انتخاب شده بارگذاری شده‌اند. این محتوا می‌تواند شامل متن، داده‌های جدولی یا هر نوع اطلاعات دیگری باشد. با استفاده از این ستون می‌توانید به راحتی به محتوای هر فایل دسترسی پیدا کرده و آن را پردازش کنید.
در خصوص این مورد تابع زیر کاراست اما قبل از اینکه تابع را بکار بگیریم آنها که سر ِ نترسی دارند در یکی از سلولهای ستون Content روی کلمه Table کلیک کنند . پس از دیدن نتیجه هر دو Step ِ ایجاد شده را حذف کنند
Pdf.Tablesاستفاده از تابع Pdf.Tables:
تابع Pdf.Tables برای تبدیل محتوای PDF به جدول استفاده می‌شود.
همچنین، می‌توانید از تابع زیر برای تغییر آیتم‌های یک ستون استفاده کنید:
Table.TransformColumns ( Source ,
{"Content" , Pdf.Tables }
)خواننده محترم کد را به شکل زیر هم می توان نوشت .
Table.TransformColumns ( Source ,
{"Content" , each Pdf.Tables(_) }
)
این کد محتوای سلولهای ستون "Content" را به جداول استخراج شده از فایل‌های PDF تبدیل می‌کند.
گسترش محتوا:
دو فلش پشت به پشت کنار سمت راست ستون Content وجود دارد. روی آن کلیک کنید و توصیه می‌شود تیک آخرین چک باکس را نگه ندارید.
سپس دکمه OK را فشار دهید.
کد زیر را خودِ پاور کوئری تدارک دیده است :
Table.ExpandTableColumn ( ConvertToPDF,
"Content", { "Id", "Name", "Kind", "Data" } , { "Id", "Name.1", "Kind", "Data" }
)
با اجرای این کد، چهار ستون جدید جایگزین ستون Content می‌شوند.
بررسی داده‌ها:
بر روی بخش خالی هر سطر در ستون Data کلیک کنید و همزمان به آیتم‌های ستون Kind توجه کنید.
اگر در ستون Kind با "Table" مواجه شدید، جداول موجود در فایل‌های PDF در ستون Data قابل مشاهده است.
اگر عبارت "Page" در ستون Kind وجود داشته باشد، علاوه بر جداول، قسمتی از متن فایل‌های PDF نیز قابل مشاهده خواهد بود.
مهم : پیشنهاد می‌شود ابتدا ردیف‌هایی که "Table" هستند را فیلتر کنید تا داده‌ها دوبل نشوند.
اهمیت مرحله Expand:
اگر فقط تیک گزینه Data را نگه دارید و فیلتر نکنید، نمی‌توانید به داده‌های جداول دسترسی پیدا کنید. بعلاوه دست آخر با انبوهی از داده های تکراری روبرو هستید مرحله Expand بسیار مهم است.
تنظیم سرستون‌ها:
برای تنظیم سرستون‌ها ، باید یک مرحله (Step) اضافه کنید و از کد زیر استفاده کنید:
با این کار سرستون‌ها در جای خود قرار می‌گیرند.
Table.TransformColumns ( Filtered ,
{ "Data" , each Table.PromoteHeaders ( _ ) }
)تغییر ترتیب ستون‌ها:
برای تغییر ترتیب ستون‌ها، تابع زیر را استفاده کنید:
با اجرای این کد، پاور کوئری سرستون‌ها را استخراج کرده و ترتیب آنها را بر اساس لیست ColumnList تنظیم می‌کند.ColumnList لیستی از سر ستونهاست به ترتیبی که کاربر تصمیم می گیرد باشند . آن را در فایل ضمیمه می توانید ببینید
تابع Table.ReorderColumns یک تابع قدرتمند در پاور کوئری است که به شما اجازه می‌دهد ترتیب ستون‌های یک جدول را تغییر دهید.
ویژگی‌های اصلی
تغییر ترتیب ستون‌ها: می‌توانید ترتیب دلخواه ستون‌ها را مشخص کنید
حذف ستون‌های اضافی: ستون‌هایی که در لیست جدید نباشند، حذف می‌شوند
برخورد با ستون‌های وجود نداشته: با پارامتر MissingField می‌توانید تعیین کنید چگونه با ستون‌های موجود برخورد شود
Table.TransformColumns(Filtered ,
{
"Data" , each
Table.ReorderColumns(
Table.PromoteHeaders(_), ColumnList)
}
)
پاور کوئری به هر ردیف از ستون "Data" نگاه می‌کند ابتدا سرستون‌ها را از اولین ردیف استخراج کرده (PromoteHeaders)و آنها را به عنوان نام ستون‌های جدید قرار می‌دهد سپس ترتیب ستون‌ها را بر اساس لیست ColumnList تنظیم می‌کند
نهایی‌سازی داده‌ها:
حالا روی فضای خالی هر سلول در ستون Data کلیک کنید تا داده‌های صحیح و یکدست را مشاهده کنید.
بر روی ستون Data کلیک راست کرده و گزینه Drill Down را انتخاب کنید. بعد از استفاه از این گزینه با کد داخل پرانتز در تصویر زیر مواجه می شوید . حالا لیستی از Table ها دارید که قرار است این ها را با تابع Table.Combine زیر هم بچینید شبیه آنچه که در تصویر می بینید در فرمول بار آن را اضافه کنید .
خوب است بدانید که برای خواندن فایل‌های PDF از یک پوشه، علاوه بر تابع Pdf.Tables، دو روش دیگر نیز وجود دارد که می‌توانید آنها را در ویدئوهای آقای میدانی یاد بگیرید
نکاتی در باره باز کردن انواع فایل های موجود در یک پوشه
فایل‌های CSV و Text
برای باز کردن و ترکیب فایل‌های CSV یا Text می‌توانید از روشی مشابه همین مقاله استفاده کنید.
این فایل‌ها معمولاً فقط شامل داده‌ها هستند و پاراگراف بین داده‌ها وجود ندارد.
به جای تابع Pdf.Table، از تابع Csv.Document استفاده کنید.
فایل‌های اکسل و Access
برای فایل‌های اکسل در یک پوشه: از تابع Excel.Workbook
برای فایل‌های Access در یک پوشه: از تابع Access.Database استفاده کنید
این توابع باید همراه با Table.TransformColumns به کار روند
نکته مهم درباره فایل‌های Access
در هر فایل Access معمولاً چند جدول وجود دارد
پاور کوئری امکان انتخاب و ترکیب فقط یک جدول خاص از هر فایل را دارد
به عنوان اولین نفر، تجربه یا دیدگاه خود را بنویسید!