فصل ۵: داده‌ی تمیز، جدول‌ها و Power Query

Power Query ۱: مفاهیم، مراحل کاربردی و ادغام خودکار فایل‌های یک پوشه

کاری که هر ماه دستی می‌کنید، یک بار ضبط کنید

فرض کنید هر ماه نمایندگان فروش فایل‌های جداگانه می‌فرستند و شما آن‌ها را باز می‌کنید، کپی می‌کنید زیر هم، ستون‌های اضافه را حذف می‌کنید، «ی» را درست می‌کنید و تاریخ‌ها را اصلاح می‌کنید. Power Query (تب Data، گروه Get & Transform Data) همه‌ی این مراحل را یک بار ثبت می‌کند و از آن پس با یک Refresh همه را روی داده‌ی جدید تکرار می‌کند. Power Query در Excel 2016 به بعد داخلی است.

مفاهیم

مفهومتوضیح
Queryیک دستورالعمل: از کجا بخوان، چه تغییراتی بده
Applied Stepsفهرست مراحل در سمت پنل؛ هر مرحله قابل ویرایش، حذف و جابه‌جایی
Mزبان پشت صحنه؛ Home ← Advanced Editor کد کامل را نشان می‌دهد
Close & Load Toمقصد خروجی: Table، PivotTable، Only Create Connection یا Data Model
Refreshاجرای دوباره‌ی همه‌ی مراحل روی داده‌ی تازه (Ctrl+Alt+F5 برای همه)

ادغام همه‌ی فایل‌های یک پوشه

  1. همه‌ی فایل‌های ماهانه را در یک پوشه بگذارید (مثلاً D:\Kashan\Sales) با ساختار ستونی یکسان.
  2. Data ← Get Data ← From File ← From Folder ← انتخاب پوشه ← Combine & Transform Data.
  3. یک فایل نمونه و شیت یا جدول مورد نظر را انتخاب کنید. Power Query یک «Sample File» و تابع تبدیل می‌سازد و همه‌ی فایل‌ها را زیر هم می‌چیند، با ستونی به نام Source.Name که نام فایل مبدأ را نشان می‌دهد.
  4. ستون‌های اضافه را حذف کنید، نوع داده‌ها را تنظیم کنید و Close & Load.
  5. ماه بعد: فایل جدید را در پوشه بیندازید و Refresh بزنید.

برای فایل‌های CSV فارسی، معادل کد M این کار، با تعیین صریح UTF-8 و پاک‌سازی حروف:

let
    Source   = Folder.Files("D:\Kashan\Sales"),
    CsvOnly  = Table.SelectRows(Source, each [Extension] = ".csv"),
    WithData = Table.AddColumn(CsvOnly, "T", each
                 Table.PromoteHeaders(
                   Csv.Document([Content], [Delimiter = ",", Encoding = 65001]),
                   [PromoteAllScalars = true])),
    Keep     = Table.SelectColumns(WithData, {"Name", "T"}),
    Expanded = Table.ExpandTableColumn(Keep, "T",
                 {"Date", "Rep", "Province", "Design", "Reed", "Area", "Amount"}),
    FixFa    = Table.TransformColumns(Expanded, {
                 {"Rep", each Text.Trim(Text.Replace(Text.Replace(_, "ي", "ی"), "ك", "ک")), type text},
                 {"Province", each Text.Trim(Text.Replace(Text.Replace(_, "ي", "ی"), "ك", "ک")), type text}}),
    Typed    = Table.TransformColumnTypes(FixFa,
                 {{"Date", type date}, {"Reed", Int64.Type}, {"Area", type number}, {"Amount", Int64.Type}},
                 "en-US")
in
    Typed

آرگومان آخر TransformColumnTypes («en-US») فرهنگ تفسیر متن است؛ یعنی تاریخ «2025-03-21» و عدد «1250000.5» مستقل از تنظیمات منطقه‌ای ویندوز کاربر درست خوانده می‌شوند.

نکته‌هایی که کمتر کسی می‌داند

  • مرحله‌ی خودکار «Changed Type» که Power Query بلافاصله پس از سرستون‌ها اضافه می‌کند، نام همه‌ی ستون‌ها را در کد ثابت می‌کند؛ اگر ماه بعد یک ستون نامش عوض شود، Refresh خطا می‌دهد. این مرحله را حذف و نوع‌ها را در انتها، فقط برای ستون‌های لازم، تنظیم کنید.
  • زبان M به بزرگی و کوچکی حروف حساس است: «Kashan» و «kashan» دو مقدار متفاوت‌اند و نام توابع باید دقیق نوشته شود (Text.Trim، نه text.trim).
  • مسیر پوشه را از Data ← Get Data ← Data Source Settings ← Change Source عوض کنید؛ یا بهتر، یک Parameter (Home ← Manage Parameters) بسازید تا همکارتان فقط مسیر را تغییر دهد.
  • فایل‌های موقت اکسل (با پیشوند ~$) که هنگام باز بودن یک فایل در پوشه ساخته می‌شوند، ادغام را خراب می‌کنند؛ در مرحله‌ی اول آن‌ها را با فیلتر روی ستون Name حذف کنید.
  • Query با «Only Create Connection» هیچ جایی از شیت را اشغال نمی‌کند اما می‌تواند منبع Query‌های دیگر یا Pivot باشد؛ برای مراحل میانی همین را انتخاب کنید.
درس قبلی درس بعدی

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.