نحوه ایجاد تغییرات ستونها در power query
در صفحه پاور کویری وقتی روی ستونی راست کلیک میکنید با توجه به نوع جنس آن ستون منویی باز می شود که البته تا حدودی مشابه هستند. اگر ستون شما عددی باشد با راست کلیک کردن روی آن منوی زیر باز می شود.
Copy : برای کپی کردن آن ستون
Remove : برای پاک کردن آن ستون، در اینصورت حجم دیتا کاهش یافته و زمان رفرش کردن دیتاها کاهش می یابد. تا جایی که می توانید فقط دیتاهایی که نیاز دارید را بیاورید و ستونهای اضافی را پاک کنید.
Remove other column : بقیه ستون ها را پاک می کند.
Duplicate column : یک ستون تکراری از روی همان ستون ایجاد می کند.
Add column from examples : با استفاده از هوش مصنوعی می تواند با وارد کردن چند دیتا از یک ستون بعنوان مثال، مابقی دیتاها را از روی ستون مورد نظر ایجاد کند. با کلیک روی این آیتم یک ستون اضافی ایجاد می شود و شما باید ستونهایی که میخواهید از آنها الگوبرداری شود را تیک بزنید و در ستون ایجاد شده در سه ردیف اول نمونه خود را تایپ کنید تا پاور کوئری مابقی ستون را حدس زده و کامل می کند. این مورد بسیار کاربردی می باشد.
Remove duplicate : ردیفهای تکراری را پاک می کند و بسیار خطرناک می باشد.
Remove errors : در آن ستون ردیف هایی که مقدار آنها error باشد را پاک می کند. Error زمانی رخ می دهد که دیتا تایپ مقدار سلول با دیتا تایپ خود ستون هاهنگ نمی باشد.
Transform : با کلیک برروی این گزینه منوی زیر باز می شود. در اینصورت می توانید مقادیر عددی را تغییر دهید بعنوان مثال به توان برسانید، لگاریتم بگیرید، قدرمطلق بگیرید، فاکتوریل بگیرید و به بالا و پایین گرد کنید.
با text transform به تکست تبدیل کنید.
Replace errors : جایگزین کردن مقدار error با مقادیر دیگر
اگر روی ستون تاریخی راست کلیک کنید منوی باز شده تا حدودی شبیه منوی قبلی که توضیح دادیم می باشد.
در اینجا منوی transform متفاوت می باشد یعنی چون ستون تاریخی می باشد تبدیل آن هم بر مبنای زمان می باشد و می توانید این تاریخ را به نام ماه ، روز و یا سال تبدیل کنید. بعنوان مثال می توانید نام روز آن تاریخ را بدست آورید. البته این تغییرات برای تاریخ میلادی می باشد و برای تاریخ شمسی کاربردی ندارد.
اگر ستون شما متنی باشد منوی transform تغییر می کند و به شکل زیر می باشد:
Trim : یعنی فاصله ابتدا و انتها را حذف کن
Clean : تمام کارکترهای غیرچاپی را پاک می کند. در واقع کارکترهایی که به چشم دیده نمی شوند را پاک می کند.
Length : طول رشته را برمیگرداند.
Json و xml : اگر ستون قابلیت تبدیل به اینها را داشته باشد با این گزینه تبدیل می کنید.
Split column : این مورد فقط برای ستونهای متنی می باشد. در واقع با این گزینه می توانید ستون متنی مورد نظر را بشکانید.
by delimiter : یعنی بر مبنای کاراکتر مشخص مورد نظر خرد می کند.
By number of characters : تعداد کاراکترها را برای خرد کردن رشته اعلام می کنید مثلا دو تا دو تا ستون را خرد کن.
By position : با توجه به شماره اندیس کاراکتر جدا می کند. مثلا از پوزیشن ۲و۴و۷ این رشته را خرد کن.
By lowercase to uppercase : برمبنای حروف کوچک به حروف بزرگ خرد می کند.
By digit to non-digit : برمبنای رقم به غیر رقم خرد می کند.
بعنوان مثال متن jon24 را می توانید با این روش به دو ستون jon و ۲۴ تفکیک کنید.
توجه کنید که فایلهای دیتای شما اگه اکسل هستن باید بصورت ستونی باشند مثلا ستون شهر، ستون کالاها، ستون میزان فروش، ستون تاریخ و …
اگر فایل شما ستونی نبود باید تغییرات لازم را در پاور کوئری انجام دهید.
تب transform :
Transpose : تب transform گزینه transpose باعث می شود جدول شما ۹۰ درجه چرخش داشته باشد.
Use first row as headers : سطر اول را بعنوان هدر در نظر میگیرد.
Fill : برای پر کردن قسمتهای null می باشد. down در سلولهای خالی پایینی کپی می کند و up یعنی از سلول محتوا دار در سلولهای خالی بالاتر کپی می کند.
Pivot و unpivot :
اگر تعدادی سرستون داشتید که خواستید همه آنها را بعنوان ردیف قرار دهید باید همه سرستونها را انتخاب کرده (برای انتخاب ستونها دکمه شیفت را نگه دارید و با دکمه arrow key مابقی را کلیک کنید) سپس روی آنها راست کلیک کرده و unpivot columns را زده در این حالت سرستونها را در یک ستون ، در ردیف های زیر هم قرار می دهد که به این کار unpivot می گویند و برعکس این قضیه را pivot می گویند.
بنابراین برای اینکه چندین ستون را به یک ستون تبدیل کنیم باید unpivot انجام دهیم.
