إنشاء صيغ Power Query في Excel

يوفر محرر Power Query استعلام بيانات وتجربة تشكيل ل Excel يمكنك استخدامها لإعادة تشكيل البيانات من العديد من مصادر البيانات. لعرض نافذة محرر Power Query، قم باستيراد البيانات من مصادر بيانات خارجيةفي ورقة عمل Excel، وحدد خلية في البيانات، ثم حدد استعلام > تحرير. فيما يلي ملخص للمكونات الرئيسية.

1. شريط محرر Power Query الذي تستخدمه لتشكيل بياناتك

2. جزء Queries الذي تستخدمه لتحديد موقع مصادر البيانات والجداول

3. قوائم السياق التي تعد اختصارات ملائمة للأوامر في الشريط

4. معاينة البيانات التي تعرض نتائج الخطوات المطبقة على البيانات

5. جزء إعدادات الاستعلام الذي يسرد الخصائص وكل خطوة في الاستعلام

خلف الكواليس، تستند كل خطوة في استعلام إلى صيغة مرئية في شريط الصيغة.

قد تكون هناك أوقات تريد فيها تعديل صيغة أو إنشائها. تستخدم الصيغ Power Query لغة الصيغة، والتي يمكنك استخدامها لإنشاء تعبيرات بسيطة ومعقدة. لمزيد من المعلومات حول بناء الجملة والوسيطات والملاحظات والوظائف والأمثلة، راجع Power Query لغة صيغة M.

باستخدام قائمة ببطولات كرة القدم كمثال، استخدم Power Query لأخذ البيانات الأولية التي وجدتها على موقع ويب وتحويلها إلى جدول منسق بشكل جيد. شاهد كيفية إنشاء خطوات الاستعلام والصيغ المقابلة لكل مهمة في جزء إعدادات الاستعلام ضمن الخطوات المطبقة وفي شريط الصيغة.

الإجراء

1. لاستيراد البيانات، حدد البيانات > من ويب، وأدخل "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" في مربع عنوان URL ، ثم حدد موافق.

2. في مربع الحوار Navigator ، حدد الجدول Results [Edit] على اليسار، ثم حدد Transform Data في الأسفل. يظهر محرر Power Query.

3. لتغيير اسم الاستعلام الافتراضي، في جزء Query Settings ، ضمن Properties، احذف "Results [Edit]" ثم أدخل "UEFA champs".

4. لإزالة الأعمدة غير المرغوب فيها، حدد الأعمدة الأولى والرابعا والخامسة، ثم حدد الصفحة الرئيسية > إزالة العمود > إزالة أعمدة أخرى.

5. لإزالة القيم غير المرغوب فيها، حدد Column1، وحدد Home > Replace Values، وأدخل "details" في المربع Values to Find، ثم حدد OK.

6. لإزالة الصفوف التي تحتوي على الكلمة "Year"، حدد سهم عامل التصفية في Column1، وقم بإلغاء تحديد خانة الاختيار بجوار "Year"، ثم حدد OK.

7. لإعادة تسمية رؤوس الأعمدة، انقر نقرا مزدوجا فوق كل منها ثم قم بتغيير "Column1" إلى "Year" و"Column4" إلى "Winner" و"Column5" إلى "Final Score".

8. لحفظ الاستعلام، حدد Home > Close & Load.

النتيجة

الجدول التالي هو ملخص لكل خطوة مطبقة والصيغة المقابلة.

خطوة الاستعلام ومهمته	الصيغة
المصدر الاتصال بمصدر بيانات ويب	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
التنقل تحديد الجدول للاتصال	=Source{2}[Data]
النوع الذي تم تغييره تغيير أنواع البيانات (التي Power Query تلقائيا)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
الأعمدة الأخرى التي تمت إزالتها إزالة أعمدة أخرى لعرض الأعمدة الهامة فقط	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
القيمة المستبدلة استبدال القيم لتنظيف القيم في عمود محدد	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
الصفوف المصفاة تصفية القيم في عمود	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
أعمدة تمت إعادة تسميتها رؤوس الأعمدة التي تم تغييرها لتكون ذات معنى	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

مهم    كن حذرا في تحرير خطوات المصدر والتنقل والنوع المتغير لأنها تم إنشاؤها بواسطة Power Query لتعريف مصدر البيانات وإعداده.

إظهار شريط الصيغة أو إخفاؤه

يظهر شريط الصيغة بشكل افتراضي، ولكن إذا لم يكن مرئيا، يمكنك إعادة عرضه.

• حدد عرضتخطيط > > شريط الصيغة.

Edit صيغة في شريط الصيغة

1. لفتح استعلام، حدد موقع استعلام تم تحميله مسبقا من محرر Power Query، وحدد خلية في البيانات، ثم حدد استعلام > تحرير. لمزيد من المعلومات، راجع إنشاء استعلام أو تحميله أو تحريره في Excel.

2. في جزء إعدادات الاستعلام ، ضمن الخطوات المطبقة، حدد الخطوة التي تريد تحريرها.

3. في شريط الصيغة، حدد موقع قيم المعلمات وقم بتغييرها، ثم حدد أيقونة Enter أو اضغط على مفتاح الإدخال Enter. على سبيل المثال، قم بتغيير هذه الصيغة للاحتفاظ أيضا ب Column2:
   
   Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   بعد:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. حدد أيقونة إدخال أو اضغط على مفتاح الإدخال Enter لمشاهدة النتائج الجديدة المعروضة في معاينة البيانات.

5. للاطلاع على النتيجة في ورقة عمل Excel، حدد الصفحة الرئيسية > إغلاق & تحميل.

إنشاء صيغة في شريط الصيغة

للحصول على مثال صيغة بسيط، دعنا نحول قيمة نصية إلى حالة مناسبة باستخدام الدالة Text.Proper.

1. لفتح استعلام فارغ، في Excel، حدد البيانات > الحصول على البيانات > من مصادر أخرى > استعلام فارغ. لمزيد من المعلومات، راجع إنشاء استعلام أو تحميله أو تحريره في Excel.

2. في شريط الصيغة، أدخل=Text.Proper("text value")، ثم حدد أيقونة إدخال أو اضغط على مفتاح الإدخال Enter.
   
   يتم عرض النتائج في معاينة البيانات .

3. للاطلاع على النتيجة في ورقة عمل Excel، حدد الصفحة الرئيسية > إغلاق & تحميل.

النتيجة:

 عند إنشاء صيغة، Power Query التحقق من صحة بناء جملة الصيغة. ومع ذلك، عند إدراج خطوة وسيطة أو إعادة ترتيبها أو حذفها في استعلام، قد تقوم بكسر استعلام.  تحقق دائما من النتائج في معاينة البيانات.

مهم    كن حذرا في تحرير خطوات المصدر والتنقل والنوع المتغير لأنها تم إنشاؤها بواسطة Power Query لتعريف مصدر البيانات وإعداده.

تحرير صيغة باستخدام مربع حوار

يستخدم هذا الأسلوب مربعات الحوار التي تختلف وفقا للخطوة. لا تحتاج إلى معرفة بناء جملة الصيغة.

1. لفتح استعلام، حدد موقع استعلام تم تحميله مسبقا من محرر Power Query، وحدد خلية في البيانات، ثم حدد استعلام > تحرير. لمزيد من المعلومات، راجع إنشاء استعلام أو تحميله أو تحريره في Excel.

2. في جزء إعدادات الاستعلام ، ضمن الخطوات المطبقة، حدد أيقونة تحرير الإعدادات للخطوة التي تريد تحريرها أو انقر بزر الماوس الأيمن فوق الخطوة، ثم حدد تحرير الإعدادات.

3. في مربع الحوار، قم بإجراء التغييرات، ثم حدد موافق.

إدراج خطوة

بعد إكمال خطوة استعلام تعيد تشكيل بياناتك، تتم إضافة خطوة استعلام أسفل خطوة الاستعلام الحالية. ولكن عند إدراج خطوة استعلام في منتصف الخطوات، قد يحدث خطأ في الخطوات اللاحقة. يعرض Power Query تحذير إدراج خطوة عند محاولة إدراج خطوة جديدة وتغير الخطوة الجديدة الحقول، مثل أسماء الأعمدة، المستخدمة في أي من الخطوات التي تتبع الخطوة المدرجة.

1. في جزء إعدادات الاستعلام ، ضمن الخطوات المطبقة، حدد الخطوة التي تريد أن تسبق الخطوة الجديدة والصيغة المقابلة لها مباشرة.

2. حدد الأيقونة إضافة خطوة إلى يسار شريط الصيغة. بدلا من ذلك، انقر بزر الماوس الأيمن فوق خطوة ثم حدد إدراج خطوة بعد. يتم إنشاء صيغة جديدة بالتنسيق :
   
   = <nameOfTheStepToReference>، مثل =Production.WorkOrder.

3. اكتب الصيغة الجديدة باستخدام التنسيق:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   على سبيل المثال، افترض أن لديك جدولا يحتوي على العمود الجنس وتريد إضافة عمود بالقيمة "Ms". أو "السيد"، اعتمادا على جنس الشخص. ستكون الصيغة:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

إعادة ترتيب خطوة

• في جزء إعدادات الاستعلامات ضمن الخطوات المطبقة، انقر بزر الماوس الأيمن فوق الخطوة، ثم حدد نقل لأعلى أو نقل لأسفل.

حذف خطوة

• حدد الأيقونة Delete إلى يسار الخطوة، أو انقر بزر الماوس الأيمن فوق الخطوة، ثم حدد Delete أو Delete Until End. تتوفر أيضا أيقونة حذف على يسار شريط الصيغة.

في هذا المثال، دعنا نحول النص في عمود إلى حالة مناسبة باستخدام مجموعة من الصيغ في المحرر المتقدم. 

على سبيل المثال، لديك جدول Excel، يسمى الطلبات، مع عمود ProductName الذي تريد تحويله إلى حالة مناسبة. 

قبل:

بعد:

عند إنشاء استعلام متقدم، يمكنك إنشاء سلسلة من خطوات صيغ الاستعلام استنادا إلى تعبير let. استخدم تعبير let لتعيين الأسماء وحساب القيم التي تتم الإشارة إليها بعد ذلك بواسطة عبارة in ، والتي تحدد الخطوة. يرجع هذا المثال نفس النتيجة التي ترجعها النتيجة في المقطع "إنشاء صيغة في شريط الصيغة".

let  
    Source = Text.Proper("hello world")
in  
    Source  

سترى أن كل خطوة تعتمد على خطوة سابقة بالإشارة إلى خطوة بالاسم. للتذكير، لغة صيغة Power Query حساسة لحالة الأحرف.

المرحلة 1: فتح المحرر المتقدم

1. في Excel، حدد البيانات > الحصول على البيانات > مصادر أخرى > استعلام فارغ. لمزيد من المعلومات، راجع إنشاء استعلام أو تحميله أو تحريره في Excel.

2. في محرر Power Query، حدد Home > Advanced Editor، الذي يفتح بقالب تعبير let.

المرحلة 2: تحديد مصدر البيانات

1. إنشاء تعبير let باستخدام الدالة Excel.CurrentWorkbook كما يلي:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   #x4Source

2. لتحميل الاستعلام إلى ورقة عمل، حدد تم، ثم حدد الصفحة الرئيسية > إغلاق & تحميل > إغلاق & تحميل.

النتيجة:

المرحلة 3: ترقية الصف الأول إلى الرؤوس

1. لفتح الاستعلام، من ورقة العمل، حدد خلية في البيانات، ثم حدد استعلام > تحرير. لمزيد من المعلومات، راجع إنشاء استعلام أو تحميله أو تحريره في Excel (Power Query).

2. في محرر Power Query، حدد Home > Advanced Editor، والذي يفتح باستخدام العبارة التي أنشأتها في المرحلة 2: تعريف مصدر البيانات.

3. في تعبير let، أضف #"First Row as Header" وTable.PromoteHeaders على النحو التالي:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
       #"First Row as Header"#x3#"First Row as Header" = Table.PromoteHeaders(Source)
   

4. لتحميل الاستعلام إلى ورقة عمل، حدد تم، ثم حدد الصفحة الرئيسية > إغلاق & تحميل > إغلاق & تحميل.

النتيجة:

المرحلة الرابعة: تغيير كل قيمة في عمود إلى حالة مناسبة

1. لفتح الاستعلام، من ورقة العمل، حدد خلية في البيانات، ثم حدد استعلام > تحرير. لمزيد من المعلومات، راجع إنشاء استعلام أو تحميله أو تحريره في Excel.

2. في محرر Power Query، حدد Home > Advanced Editor، الذي يفتح باستخدام العبارة التي أنشأتها في المرحلة 3: ترقية الصف الأول إلى الرؤوس.

3. في تعبير let، قم بتحويل كل قيمة عمود ProductName إلى نص مناسب باستخدام الدالة Table.TransformColumns، مع الإشارة إلى خطوة صيغة الاستعلام "الصف الأول كعنوان" السابقة، وإضافة #"Capitalized Each Word" إلى مصدر البيانات، ثم تعيين #"Capitalized Each Word" إلى النتيجة الواردة.
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. لتحميل الاستعلام إلى ورقة عمل، حدد تم، ثم حدد الصفحة الرئيسية > إغلاق & تحميل > إغلاق & تحميل.

النتيجة:

يمكنك التحكم في سلوك شريط الصيغة في محرر Power Query لكافة المصنفات.

عرض شريط الصيغة أو إخفاؤه

1. حدد خيارات وإعدادات> الملفات > خيارات الاستعلام.

2. في الجزء الأيسر، ضمن GLOBAL، حدد محرر Power Query.

3. في الجزء الأيمن، ضمن تخطيط، حدد عرض شريط الصيغة أو قم بإلغاء تحديده.

تشغيل M Intellisense أو إيقاف تشغيله

1. حدد خيارات وإعدادات> الملف > خيارات الاستعلام .

2. في الجزء الأيسر، ضمن GLOBAL، حدد محرر Power Query.

3. في الجزء الأيمن، ضمن الصيغة، حدد أو قم بإلغاء تحديد تمكين M Intellisense في شريط الصيغة والمحرر المتقدم ومربع حوار العمود المخصص.

ملاحظة    سيدخل تغيير هذا الإعداد حيز التنفيذ في المرة التالية التي تفتح فيها نافذة محرر Power Query.