বিষয়বস্তু
আপনি যদি রেগুলার এক্সপ্রেশনগুলির সাথে অন্তত কিছুটা পরিচিত হন তবে আপনাকে সেগুলির বিজ্ঞাপন দেওয়ার দরকার নেই। আপনি যদি এই বিষয়ে পুরোপুরি না হন, তাহলে রেগুলার এক্সপ্রেশন (রেগুলার এক্সপ্রেশন = RegExp = “regexps” = “regulars”) এমন একটি ভাষা যেখানে বিশেষ অক্ষর এবং নিয়ম ব্যবহার করে, প্রয়োজনীয় সাবস্ট্রিংগুলি পাঠ্যে অনুসন্ধান করা হয়, সেগুলি বের করা হয়। অথবা অন্য টেক্সট দিয়ে প্রতিস্থাপিত। এটি একটি খুব শক্তিশালী এবং সুন্দর টুল, টেক্সটের সাথে কাজ করার অন্যান্য সমস্ত উপায়ের থেকে উচ্চতর মাত্রার একটি ক্রম।
আমি ইতিমধ্যেই বিশদভাবে বর্ণনা করেছি এবং জীবন থেকে উদাহরণের একটি গুচ্ছ সহ বর্ণনা করেছি কিভাবে আপনি সাধারণ ম্যাক্রো ব্যবহার করে এক্সেলে রেগুলার এক্সপ্রেশন সমর্থন যোগ করতে পারেন – আপনি যদি এই নিবন্ধটি না পড়ে থাকেন তবে আমি আপনাকে এটি চালিয়ে যাওয়ার আগে এটি পড়ার পরামর্শ দিচ্ছি। আপনি অনেক নতুন জিনিস আবিষ্কার করবেন, আমি গ্যারান্টি 🙂
যাইহোক, প্রশ্নটি উন্মুক্ত রয়েছে – পাওয়ার কোয়েরিতে রেগুলার এক্সপ্রেশন ব্যবহার করার ক্ষমতা কীভাবে যুক্ত করবেন? পাওয়ার ক্যোয়ারী, অবশ্যই, নিজে থেকে ভাল এবং পাঠ্যের সাথে অনেক কিছু করতে পারে (কাটিং, আঠা, পরিষ্কার করা, ইত্যাদি), তবে আপনি যদি নিয়মিত অভিব্যক্তির শক্তি দিয়ে এটি অতিক্রম করতে পারেন তবে এটি কেবল একটি বোমা হবে।
দুর্ভাগ্যবশত, পাওয়ার কোয়েরিতে RegExps-এর সাথে কাজ করার জন্য কোনও অন্তর্নির্মিত ফাংশন নেই, এবং অফিসিয়াল Microsoft সহায়তা এবং প্রযুক্তিগত সহায়তা এই প্রশ্নের উত্তর নেতিবাচকভাবে দেয়। যাইহোক, এই সীমাবদ্ধতার কাছাকাছি একটি উপায় আছে 🙂
পদ্ধতির সারমর্ম
প্রধান ধারণা অসম্মান করা সহজ.
অন্তর্নির্মিত পাওয়ার কোয়েরি ক্ষমতার তালিকায়, একটি ফাংশন রয়েছে ওয়েব পেজ. অফিসিয়াল মাইক্রোসফ্ট সহায়তা সাইটে এই ফাংশনটির বর্ণনা অত্যন্ত সংক্ষিপ্ত:
অনুবাদিত, এটি হবে: "এইচটিএমএল ডকুমেন্টের বিষয়বস্তুগুলিকে এর উপাদান কাঠামোতে বিভক্ত করে, সেইসাথে সম্পূর্ণ নথির একটি উপস্থাপনা এবং ট্যাগগুলি সরানোর পরে এর বডি ফেরত দেয়।" তাই-তাই বর্ণনা, অকপটে.
সাধারণত ওয়েব থেকে ডেটা আমদানি করার সময় এই ফাংশনটি ব্যবহার করা হয় এবং স্বয়ংক্রিয়ভাবে প্রতিস্থাপিত হয়, উদাহরণস্বরূপ, যখন আমরা ট্যাবে নির্বাচন করি উপাত্ত আদেশ ইন্টারনেট থেকে (ডেটা — ওয়েব থেকে). আমরা ফাংশনটিকে একটি আর্গুমেন্ট হিসাবে একটি ওয়েব পেজ দিই, এবং এটি পূর্বে সমস্ত ট্যাগ সাফ করে টেবিলের আকারে এর বিষয়বস্তু আমাদের কাছে ফেরত দেয়।
সাহায্য যা বলে না তা হল এইচটিএমএল মার্কআপ ভাষা ছাড়াও ক্রিয়া ওয়েব পেজ জাভাস্ক্রিপ্ট স্ক্রিপ্ট সমর্থন করে, যা এখন ইন্টারনেটে ওয়েবসাইটগুলিতে সর্বব্যাপী। এবং জাভাস্ক্রিপ্ট, পরিবর্তে, সবসময় রেগুলার এক্সপ্রেশনের সাথে কাজ করতে সক্ষম হয়েছে এবং RegExps-এর জন্য বিল্ট-ইন ফাংশন রয়েছে! তাই Power Query-এ রেগুলার এক্সপ্রেশন ইমপ্লিমেন্ট করার জন্য, আমাদের একটি ছোট জাভাস্ক্রিপ্ট প্রোগ্রামের আর্গুমেন্ট হিসাবে Web.Page ফাংশনগুলিকে ফিড করতে হবে যা পাওয়ার কোয়েরির জন্য সমস্ত কাজ করবে।
এটা বিশুদ্ধ জাভাস্ক্রিপ্ট মত দেখায় কি
ইন্টারনেটে জাভাস্ক্রিপ্টে রেগুলার এক্সপ্রেশন নিয়ে কাজ করার জন্য অনেক বিস্তারিত টিউটোরিয়াল রয়েছে (উদাহরণস্বরূপ, এক, দুই)।
সংক্ষেপে এবং সরলীকৃতভাবে, জাভাস্ক্রিপ্ট কোডটি দেখতে এইরকম হবে:
এখানে:
- var str = 'সসেজের জন্য 123 এবং 789 বিল পরিশোধ করুন'; - একটি পরিবর্তনশীল তৈরি করুন Str এবং এটিকে উৎস টেক্সট বরাদ্দ করুন যা আমরা বিশ্লেষণ করব।
- var প্যাটার্ন = /d+/gi; - একটি রেগুলার এক্সপ্রেশন তৈরি করুন এবং এটি একটি ভেরিয়েবলে রাখুন প্যাটার্ন.
অভিব্যক্তিটি একটি স্ল্যাশ (/) দিয়ে শুরু হয়।
এখানে অভিব্যক্তি নিজেই, উদাহরণস্বরূপ, হয় d+ অঙ্কের যেকোনো ক্রম বোঝায়।
অভিব্যক্তির পরে ভগ্নাংশের মাধ্যমে, অতিরিক্ত অনুসন্ধান পরামিতি (সংশোধনকারী) রয়েছে – সেগুলি যে কোনও ক্রমে নির্দিষ্ট করা যেতে পারে:
- g – মানে বিশ্বব্যাপী অনুসন্ধান, অর্থাৎ একটি মিল খুঁজে পাওয়ার পরে, আপনার থামানো উচিত নয়, তবে পাঠ্যের শেষ না হওয়া পর্যন্ত অনুসন্ধান চালিয়ে যাওয়া উচিত। যদি এই সংশোধকটি সেট করা না থাকে, তাহলে আমাদের স্ক্রিপ্টটি শুধুমাত্র প্রথম ম্যাচটি ফেরত দেবে (123)
- i - চিঠির ক্ষেত্রে বিবেচনা না করে অনুসন্ধান করুন
- m - বহু-লাইন অনুসন্ধান (যখন উত্স পাঠ্যটি কয়েকটি লাইনে বিভক্ত হয় তখন ব্যবহৃত হয়)
- var ফলাফল = str.match(pattern).join(';'); - উত্স পাঠে একটি অনুসন্ধান করুন (Strপ্রদত্ত রেগুলার এক্সপ্রেশন দ্বারা (প্যাটার্ন) এবং একটি পরিবর্তনশীল ফলাফল রাখুন ফল, কমান্ড ব্যবহার করে একটি সেমিকোলন দিয়ে তাদের সংযুক্ত করা যোগদানের
- document.write(ফলাফল); - ফলাফল পরিবর্তনশীল বিষয়বস্তু প্রদর্শন
এছাড়াও মনে রাখবেন যে জাভাস্ক্রিপ্টে টেক্সট স্ট্রিংগুলি (রেগুলার এক্সপ্রেশন ব্যতীত) অ্যাপোস্ট্রোফিসে আবদ্ধ থাকে, পাওয়ার কোয়েরি বা VBA-তে উদ্ধৃতি নয়।
আউটপুটে, এই স্ক্রিপ্টটি উত্স পাঠে পাওয়া সমস্ত সংখ্যার ফলস্বরূপ আমাদের দেবে:
123, 789
জাভাস্ক্রিপ্ট শর্ট কোর্স শেষ, সবাইকে ধন্যবাদ। আপনি যুক্তি পেতে আশা করি 🙂
এই নির্মাণটি পাওয়ার কোয়েরিতে স্থানান্তর করা বাকি আছে।
পাওয়ার ক্যোয়ারীতে রেগুলার এক্সপ্রেশন দ্বারা টেক্সট ফাংশন সার্চ এবং এক্সট্রাক্ট করুন
আমরা নিম্নলিখিত করি:
1. এক্সেল খুলুন এবং ট্যাবে একটি নতুন খালি পাওয়ার কোয়েরি তৈরি করুন ডেটা - ডেটা পান / অনুরোধ তৈরি করুন - অন্যান্য উত্স থেকে - খালি অনুরোধ (ডেটা — ডেটা পান / নতুন ক্যোয়ারী — অন্যান্য উৎস থেকে — ফাঁকা ক্যোয়ারী). যদি আপনার কাছে এক্সেল 2010-2013 এবং পাওয়ার কোয়েরির একটি পুরানো সংস্করণ থাকে যা আপনার বিল্ট-ইন না থাকে তবে একটি পৃথক অ্যাড-ইন হিসাবে ইনস্টল করা হয়েছিল, তবে এই সমস্ত ট্যাবে থাকবে পাওয়ার কোয়েরিএবং না উপাত্ত.
2. ক্যোয়ারী সম্পাদকের খালি উইন্ডোতে যা খোলে, ডান প্যানেলে, অবিলম্বে আমাদের ভবিষ্যতের ফাংশনের নাম লিখুন (উদাহরণস্বরূপ, fxRegExpExtract)
3. চলুন ট্যাবে যাই দেখুন - অ্যাডভান্সড এডিটর (দেখুন — অ্যাডভান্সড এডিটর), আমরা খালি অনুরোধের সম্পূর্ণ M-কোড মুছে ফেলি এবং সেখানে আমাদের সুপারফাংশনের কোড পেস্ট করি:
আপনার হাত দেখুন:
প্রথম লাইনে, আমরা বলি যে আমাদের ফাংশনে তিনটি পাঠ্য আর্গুমেন্ট থাকবে: পাঠ্য - মূল পাঠ্য বিশ্লেষণ করা হচ্ছে, regex - নিয়মিত এক্সপ্রেশন প্যাটার্ন, ডিলিম - ফলাফল প্রদর্শনের জন্য সীমাবদ্ধ অক্ষর।
পরবর্তী আমরা ফাংশন কল ওয়েব পেজ, তার যুক্তিতে উপরে বর্ণিত জাভাস্ক্রিপ্ট কোড গঠন করে। আমরা কোডে আমাদের পরিবর্তনশীল আর্গুমেন্ট পেস্ট করি এবং প্রতিস্থাপন করি।
টুকরা:
[ডেটা]{0}[শিশু]{0}[শিশু]{1}[পাঠ্য]{0}
… আমাদের প্রয়োজনীয় ফলাফলের সাথে টেবিলে "পড়ে যাওয়ার" প্রয়োজন। বিন্দু যে ফাংশন ওয়েব পেজ ফলস্বরূপ, এটি বেশ কয়েকটি নেস্টেড টেবিল তৈরি করে যা একটি ওয়েব পৃষ্ঠার কাঠামোর পুনরাবৃত্তি করে। এম-কোডের এই অংশটি ছাড়া, আমাদের ফাংশন এটি আউটপুট করবে:
… এবং আমাদের শব্দটি কয়েকবার ক্লিক করতে হবে টেবিল, পর্যায়ক্রমে কলামে চাইল্ড নেস্টেড টেবিলের মধ্যে "পতন" শিশু:
এই সমস্ত উদ্ধৃতির পরিবর্তে, আমরা অবিলম্বে আমাদের ফাংশনের কোডে নির্দেশ করি যা টেবিল এবং কলাম নেস্ট করেছে (পাঠ) আমাদের দরকার.
এখানে, আসলে, সব গোপন. এটি বোতাম টিপুন অবশেষ শেষ জানালায় উন্নত সম্পাদক, যেখানে আমরা আমাদের কোড সন্নিবেশিত করেছি, এবং আপনি সবচেয়ে সুস্বাদুতে এগিয়ে যেতে পারেন - কর্মক্ষেত্রে আমাদের ফাংশন চেষ্টা করুন।
এখানে বীজ উদাহরণ একটি দম্পতি আছে.
উদাহরণ 1. অর্থপ্রদানের বিবরণ থেকে অ্যাকাউন্ট নম্বর এবং তারিখ পুনরুদ্ধার করা
আমাদের কাছে অর্থপ্রদানের বিবরণ (উদ্দেশ্য) সহ একটি ব্যাঙ্ক স্টেটমেন্ট রয়েছে, যেখানে আপনাকে আলাদা কলামে অর্থপ্রদানের চালানের সংখ্যা এবং তারিখগুলি বের করতে হবে:
আমরা স্ট্যান্ডার্ড উপায়ে পাওয়ার কোয়েরিতে টেবিলটি লোড করি ডেটা - টেবিল/রেঞ্জ থেকে (ডেটা — টি থেকেসক্ষম/আরফেরেশতা).
তারপরে আমরা আমাদের ফাংশনের মাধ্যমে একটি গণনা করা কলাম যুক্ত করি কলাম যোগ করুন - কাস্টম ফাংশন কল করুন (কলাম যোগ করুন — কাস্টম ফাংশন আহ্বান করুন) এবং তার আর্গুমেন্ট লিখুন:
একটি নিয়মিত অভিব্যক্তি হিসাবে (যুক্তি regex) টেমপ্লেট আমরা ব্যবহার করি:
(d{3,5}|d{2}.d{2}.d{4})
… মানুষের ভাষায় অনূদিত অর্থ:
3 থেকে 5 সংখ্যার সংখ্যা (অ্যাকাউন্ট নম্বর)
or
"2-বিট সংখ্যা - পয়েন্ট - 2-বিট সংখ্যা - বিন্দু - 4-বিট সংখ্যা" ফর্মের টুকরো, অর্থাৎ, DD.MM.YYYY ফর্মের তারিখ।
একটি সীমাবদ্ধ অক্ষর হিসাবে (যুক্তি ডিলিম) একটি সেমিকোলন লিখুন।
ক্লিক করার পরে OK আমাদের ম্যাজিক ফাংশন আমাদের রেগুলার এক্সপ্রেশন অনুযায়ী সমস্ত প্রারম্ভিক ডেটা বিশ্লেষণ করে এবং ইনভয়েসের পাওয়া সংখ্যা এবং তারিখ সহ আমাদের জন্য একটি কলাম তৈরি করে:
এটি কমান্ড ব্যবহার করে সেমিকোলন দ্বারা পৃথক করা অবশেষ হোম — স্প্লিট কলাম — ডিলিমিটার দ্বারা (হোম — স্প্লিট কলাম — ডিলিমিটার দ্বারা) এবং আমরা যা চাই তা পাই:
সৌন্দর্য!
উদাহরণ 2: পাঠ্য থেকে ইমেল ঠিকানা বের করুন
ধরুন আমাদের কাছে প্রাথমিক ডেটা হিসাবে নিম্নলিখিত টেবিল রয়েছে:
… যেখান থেকে আমাদের সেখানে পাওয়া ইমেল ঠিকানাগুলি বের করতে হবে (স্বচ্ছতার জন্য, আমি সেগুলিকে টেক্সটে লাল রঙে হাইলাইট করেছি)।
আগের উদাহরণের মতো, আমরা স্ট্যান্ডার্ড উপায়ে পাওয়ার কোয়েরিতে টেবিলটি লোড করি ডেটা - টেবিল/রেঞ্জ থেকে (ডেটা — টি থেকেসক্ষম/আরফেরেশতা).
তারপরে আমরা আমাদের ফাংশনের মাধ্যমে একটি গণনা করা কলাম যুক্ত করি কলাম যোগ করুন - কাস্টম ফাংশন কল করুন (কলাম যোগ করুন — কাস্টম ফাংশন আহ্বান করুন) এবং তার আর্গুমেন্ট লিখুন:
ইমেল ঠিকানাগুলি পার্স করা আরও কঠিন কাজ এবং এটি সমাধান করার জন্য বিভিন্ন মাত্রার দুঃস্বপ্নের নিয়মিত অভিব্যক্তি রয়েছে। আমি সহজ বিকল্পগুলির মধ্যে একটি ব্যবহার করেছি - আদর্শ নয়, তবে বেশিরভাগ ক্ষেত্রে বেশ কাজ করে:
[w|.|-]*@w*.[w|.]*
বিভাজক হিসাবে (ডিলিম) আপনি একটি সেমিকোলন এবং একটি স্থান লিখতে পারেন।
ক্লিক করুন OK এবং আমরা মূল পাঠ্য "পোরিজ" থেকে বের করা ই-মেইল ঠিকানা সহ একটি কলাম পাই:
যাদু!
PS
প্রবাদটি হিসাবে: "এমন কোন ভাল জিনিস নেই যা আরও ভাল করা যায় না।" পাওয়ার ক্যোয়ারী নিজেই দুর্দান্ত, এবং যখন রেগুলার এক্সপ্রেশনের সাথে মিলিত হয়, এটি আমাদেরকে সম্পূর্ণ অবাস্তব শক্তি দেয় এবং যেকোনো টেক্সট ডেটা প্রক্রিয়াকরণে নমনীয়তা দেয়। আমি আশা করি মাইক্রোসফ্ট একদিন পাওয়ার কোয়েরি এবং পাওয়ার বিআই আপডেটগুলিতে RegExp সমর্থন যোগ করবে এবং একটি খঞ্জনীর সাথে উপরের সমস্ত নাচগুলি অতীতের জিনিস হয়ে যাবে। ওয়েল, আপাতত, হ্যাঁ.
আমি এটাও যোগ করতে চাই যে https://regexr.com/ সাইটে রেগুলার এক্সপ্রেশনের সাথে খেলা সুবিধাজনক – সরাসরি অনলাইন এডিটরে। সেখানে বিভাগে সম্প্রদায় নিদর্শন সব অনুষ্ঠানের জন্য প্রচুর রেডিমেড নিয়মিত ঋতু রয়েছে। পরীক্ষা করুন – রেগুলার এক্সপ্রেশনের সমস্ত শক্তি এখন পাওয়ার কোয়েরিতে আপনার পরিষেবাতে!
- রেগুলার এক্সপ্রেশন (RegExp) কি এবং এক্সেল এ কিভাবে ব্যবহার করবেন
- পাওয়ার কোয়েরিতে অস্পষ্ট পাঠ্য অনুসন্ধান
- পাওয়ার কোয়েরি ব্যবহার করে বিভিন্ন ফাইল থেকে টেবিল একত্রিত করা