حل أسعار العملات المفقودة باستخدام Power Query في LuckyTemplates

في هذا البرنامج التعليمي ، ستتعلم كيفية استخدام Power Query لتحويلات العملات في LuckyTemplates. يمكنك مشاهدة الفيديو الكامل لهذا البرنامج التعليمي أسفل هذه المدونة.

ستساعدك الطريقة التي تمت مناقشتها في هذه المدونة على حل المشكلات في جدول أسعار العملات باستخدام آخر سعر متاح.

ستفهم كيفية حل الأمور عندما لا يحتوي جدول أسعار العملات على سعر عملة في كل يوم.

جدول المحتويات

• تحديد البيانات المفقودة
• فصل العملات المختلفة
• ملء الأسعار الناقصة
• دمج جميع العملات
• تكوين العلاقات في نموذج البيانات
• مقارنة بين أساليب DAX و Power Query
• خاتمة

تحديد البيانات المفقودة

يمكن أن تحدث هذه المشكلة إذا كان جدول "التاريخ" لا يحتوي على بيانات أيام العطل أو عطلات نهاية الأسبوع.

إذا لم يكن لديك سعر في أي يوم ولم تحسبه ، فلن يتم تحويل مقاييس مثل إجمالي المبيعات.

إذا ألقيت نظرة على جدول أسعار العملات هذا ، ستجد أسعاره باليورو لشهر يناير 2016.

لكن لاحظ أن اليومين السابع والعشرين والثامن والعشرين مفقودان وليس لهما معدل. لذا ، إذا ذهبت إلى المصفوفة ، يمكنك أن ترى أنه لا توجد معدلات لـ 27 و 28.

إن مقياس سعر العملة المفقود هو ببساطة مجموع جدول أسعار العملات الفعلية.

إجمالي 26.6983 في الجدول لا معنى له. إنه مجرد مجموع جميع أسعار العملات التي تعلوها والتي لا تحتاج إلى جمعها لأنها ما هي عليه في اليوم الحالي.

الآن ، يمكن إصلاح ذلك في Power Query لأن هذه مشكلة تتعلق بنمذجة البيانات والتي تتضمن الاستعلامات والتجميع. يمكن القيام بذلك أيضًا باستخدام DAX ، ولكنه أسهل وأسرع باستخدام Power Query في LuckyTemplates.

لذلك ، انقر فوق تحويل البيانات ، ثم افتح نافذة Power Query.

هذا هو الجدول الذي يحتوي على بيانات مفقودة. يمكنك أيضًا أن ترى في جدول أسعار العملات هذا أنه يحتوي على اليورو والجنيه والدولار.

ثم هذا هو الجدول النهائي الذي يحتوي على قيم في 27 و 28 يناير. هذا هو الجدول أو الإخراج الذي يجب أن يكون لديك.

قم باستيراد بياناتك من أي مصدر تحصل عليها منه وقم فقط بتغيير نوع البيانات.

فصل العملات المختلفة

بعد ذلك ، إذا كان لديك أكثر من عملة ، فمن المهم فصلها وتجميعها حسب مؤشرها.

عند تجميعهم ، تأكد من تجميعهم في جميع الصفوف بحيث إذا نقرت على اليورو على سبيل المثال ، فسوف يعرض فقط أسعار العملات باليورو.

يمكنك أيضًا أن ترى أن هناك أيامًا مفقودة هنا بالإضافة إلى عملة الجنيه.

هناك شيء يسمى استدعاء الوظيفة المخصصة في LuckyTemplates. هذه دالة مع FillMissingRates.

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

اكتشف ما تفعله الوظائف المخصصة. إذا قمت بالنقر فوق رمز الترس بالقرب من Invoke Custom Function ، يمكنك رؤية اسم العمود والمصدر الذي يتم سحبه في العمود All.

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

على سبيل المثال ، إذا كنت تريد التركيز على اليورو ، فقم بترشيحه إلى المؤشر.

لمعرفة الأيام المفقودة ، ادمجها مع جدول التواريخ.

فيما يلي جدول عملات أساسي مصفى لأسفل وجدول DimDate:

بالنسبة إلى نوع الانضمام ، يمكنك استخدام Full Outer لإحضار جميع الصفوف من كلا الجدولين. هذا يوضح لك ما هو مفقود والمعدلات المطابقة.

عند دمجها ، ستعطيك جدولًا كاملاً كجدول فرعي.

بعد ذلك ، قم بفرز الصفوف ثم قم بتوسيع DimDate لتضمين عمود التاريخ فقط.

الآن ، يمكنك أن ترى أن كل شيء متطابق. وعندما تقوم بفرز الصفوف من الأقدم إلى الأحدث ، يمكنك أيضًا مشاهدة اليومين المفقودين بقيم فارغة.

ملء الأسعار الناقصة

من المهم أن تكون بهذا الترتيب لأنك ستقوم بملء جميع القيم الخالية في جميع الأعمدة باستثناء التاريخ.

بعد أن تقوم بتعبئته ، يمكنك أن ترى أن القيم الخالية قد تغيرت إلى اليورو وآخر سعر متاح.

بعد ذلك ، قم بإزالة الأعمدة غير الضرورية مثل عمود التاريخ من الجدول.

يحتوي عمود التاريخ 1 على التواريخ بالفعل ، لذا فإن عمود التاريخ ليس ضروريًا. ثم ، قم بتعيين Crossrate إلى رقم عشري.

أخيرًا ، أعد ترتيب الأعمدة وأعد تسميتها وتأكد من تعيين أنواع البيانات الخاصة بك.

لديك الآن جدول أسعار العملات الكامل.

ومع ذلك ، هذا يعمل فقط لهذا الجدول. عليك أن تجد طريقة لتطبيق نفس المنطق على جدول يتم فيه تجميع العملات.

دمج جميع العملات

إذا كان لديك عملات مختلفة ، فمن الصعب الاحتفاظ بجداول أسعار مختلفة. لذلك ، تحتاج إلى دمجهم لإنشاء جدول عملات واحد.

للبدء ، خذ الجدول الموجود وأنشئ وظيفة بالنقر فوق جدول وتحديد إنشاء وظيفة . بعد ذلك ، أدخل اسم الوظيفة.

بعد ذلك ، انتقل إلى Advanced Editor . قم بإزالة الحسابات التي لن تحتاجها. في هذه الحالة ، تخلص من المصدر ، وعامل التصفية ، وغيّر بناء الجملة.

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

الآن ، انتقل إلى Add Column ، وانقر فوق Invoke Custom Function. أدخل اسم عمود مثل كل البيانات. بالنسبة للاستعلام الوظيفي ، حدد الوظيفة التي تم إنشاؤها مسبقًا. اختر الكل كمصدر ، ثم انقر فوق موافق.

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

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

ستحصل الآن على جدول أسعار عملات مدمج لا يحتوي على تواريخ وأسعار مفقودة.

تكوين العلاقات في نموذج البيانات

في نموذج البيانات الخاص بك ، يمكنك رؤية جدول FactCurrencyRates . الآن ، قم بإنشاء علاقة ، شريط إلى شريط و تاريخ حتى تاريخ ، مع هذا الجدول وجدول DimCurrencyRates و DimDate.

في المصفوفة الخاصة بك ، اسحب معدل العملة "لا مفقود" لترى أنه يحتوي الآن على أسعار يومي 27 و 28 تم إجراؤها في Power Query.

يعرض عمود "آخر سعر عملة تم الإبلاغ عنها" أيضًا معدلات الأيام المفقودة. يظهر نفس المعدلات والقيم. لكن الاختلاف بينهما هو أنه تم إنشاء هذا العمود في DAX.

هذا هو مقياس وبناء الجملة لهذا العمود. الأمر ليس بهذه البساطة مثل استخدام وظيفة فقط في Power Query.

بالنظر إلى الجدول ، سترى عمود المبيعات المحولة لا مفقودة. يعرض المبيعات التي تم تحويلها باستخدام عمود معدل العملة غير مفقود. يستخدم العمود الآخر الذي يحتوي على مبيعات محولة العمود الذي تم إنشاؤه في DAX.

الوظيفة المستخدمة في الحصول على المبيعات المحولة هي . إذا نظرت إلى مقياس هذا العمود ، يمكنك رؤية SUMX يمر فوق جدول DimDate.

مقارنة بين أساليب DAX و Power Query

يمكنك بالفعل مقارنة أعمدة المبيعات المحولة التي استخدمت DAX و Power Query في LuckyTemplates.

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

بعد ذلك ، افتح DAX Studio. إنه تطبيق يوضح لك كيف ولماذا تعمل الأشياء في نموذجك. بعد ذلك ، انسخ استعلام المصفوفة والصقه داخل الاستوديو.

للمقارنة ، قم بتحويل أحدهم إلى تعليق أولاً حتى لا يتداخل مع العمود الآخر الذي يتم اختباره.

وبعد ذلك ، قم بتحميل توقيتات الخادم وخطة الاستعلام. نظرًا لأنك تقارن الأداء ، امسح ذاكرة التخزين المؤقت أولاً قبل تشغيل المقارنة.

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

الآن ، قم بتشغيل العمود الآخر الذي يستخدم استعلام الطاقة. لكن أولاً ، قم بتحويل العمود الآخر في بناء الجملة إلى تعليق.

يمكنك أن ترى أن العمود لم يتم تشغيله إلا لمدة 25 مللي ثانية ولديه 7 عمليات مسح فقط.

يمكنك أن ترى بوضوح أيهما يعمل بشكل أسرع وأفضل بين أساليب DAX و Power Query.

خاتمة

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

شيء آخر حول هذه الطريقة هو أنها لا تبطئ إذا أصبحت العملية الحسابية معقدة.

ومع ذلك ، لا يعمل هذا إلا إذا لم يكن من الضروري تحويل البيانات التي تطلبها بسرعة. إذا كان الأمر كذلك ، فيجب عليك استخدام DAX.

أتمنى لك كل خير،