List.Accumulate ফাংশন সহ পাওয়ার কোয়েরিতে বাল্ক টেক্সট প্রতিস্থাপন

রেফারেন্স তালিকা অনুসারে কীভাবে দ্রুত এবং বাল্কভাবে টেক্সটটি ফর্মুলা দিয়ে প্রতিস্থাপন করবেন – আমরা ইতিমধ্যে এটি সাজিয়েছি। এখন পাওয়ার কোয়েরিতে এটি করার চেষ্টা করা যাক।

যেমন প্রায়ই হয় সম্পাদন করা এই কাজটি ব্যাখ্যা করার চেয়ে অনেক সহজ কেন এটা কাজ করে, কিন্তু চলুন দুটোই করার চেষ্টা করি 🙂

সুতরাং, আমাদের কাছে কীবোর্ড শর্টকাট সহ সাধারণ রেঞ্জ থেকে তৈরি দুটি "স্মার্ট" গতিশীল টেবিল রয়েছে জন্য ctrl+T বা দল হোম - একটি টেবিল হিসাবে বিন্যাস (হোম - টেবিল হিসাবে ফর্ম্যাট):

আমি প্রথম টেবিলে ডাকলাম উপাত্ত, দ্বিতীয় টেবিল - নির্দেশিকাক্ষেত্র ব্যবহার টেবিলের নাম (টেবিলের নাম) ট্যাব রচয়িতা (নকশা).

টাস্ক: টেবিলে ঠিকানাগুলি প্রতিস্থাপন করুন উপাত্ত একটি কলাম থেকে সমস্ত ঘটনা খুঁজতে হ্যান্ডবুক কলাম থেকে তাদের সংশ্লিষ্ট সঠিক প্রতিরূপের কাছে বিকল্প. ঘরের বাকি পাঠ্যগুলি অস্পর্শ থাকা উচিত।

ধাপ 1. পাওয়ার কোয়েরিতে ডিরেক্টরিটি লোড করুন এবং এটিকে একটি তালিকায় পরিণত করুন

রেফারেন্স টেবিলের যেকোনো স্থানে সক্রিয় সেল সেট করার পরে, ট্যাবে ক্লিক করুন উপাত্ত (তারিখ)অথবা ট্যাবে পাওয়ার কোয়েরি (যদি আপনার কাছে এক্সেলের একটি পুরানো সংস্করণ থাকে এবং আপনি একটি পৃথক ট্যাবে একটি অ্যাড-ইন হিসাবে পাওয়ার কোয়েরি ইনস্টল করেন) বোতামে টেবিল/পরিসীমা থেকে (সারণী/পরিসীমা থেকে).

রেফারেন্স টেবিলটি পাওয়ার কোয়েরি ক্যোয়ারী এডিটরে লোড করা হবে:

হস্তক্ষেপ না করার জন্য, একটি স্বয়ংক্রিয়ভাবে যোগ করা পদক্ষেপ পরিবর্তিত প্রকার (পরিবর্তিত প্রকার) ডান প্যানেলে, প্রয়োগ করা পদক্ষেপগুলি নিরাপদে মুছে ফেলা যেতে পারে, শুধুমাত্র ধাপটি রেখে উৎস (উৎস):

এখন, আরও রূপান্তর এবং প্রতিস্থাপন করতে, আমাদের এই টেবিলটিকে একটি তালিকা (তালিকা) তে পরিণত করতে হবে।

লিরিক্যাল ডিগ্রেশন

• টেবিল বেশ কয়েকটি সারি এবং কলাম নিয়ে গঠিত একটি দ্বি-মাত্রিক অ্যারে।
• রেকর্ড (রেকর্ড) - এক-মাত্রিক অ্যারে-স্ট্রিং, নাম সহ বেশ কয়েকটি ক্ষেত্র-উপাদান সমন্বিত, উদাহরণস্বরূপ [নাম = "মাশা", লিঙ্গ = "চ", বয়স = 25]
  
• তালিকা - একটি এক-মাত্রিক অ্যারে-কলাম, যেটিতে বেশ কয়েকটি উপাদান রয়েছে, উদাহরণস্বরূপ {1, 2, 3, 10, 42} or { "বিশ্বাস আশা ভালবাসা" }
  

আমাদের সমস্যা সমাধানের জন্য, আমরা প্রাথমিকভাবে টাইপের বিষয়ে আগ্রহী হব তালিকা.

এখানে কৌশলটি হল যে পাওয়ার কোয়েরির তালিকা আইটেমগুলি শুধুমাত্র সাধারণ সংখ্যা বা পাঠ্য নয়, অন্যান্য তালিকা বা রেকর্ডও হতে পারে। এটি এমন একটি জটিল তালিকা (তালিকা), রেকর্ড (রেকর্ড) নিয়ে গঠিত যা আমাদের ডিরেক্টরিটি চালু করতে হবে। পাওয়ার কোয়েরি সিনট্যাকটিক নোটেশনে (বর্গাকার বন্ধনীতে এন্ট্রি, কোঁকড়া বন্ধনীতে তালিকা) এটি দেখতে এরকম হবে:

{

    [ খুঁজুন = “সেন্ট. পিটার্সবার্গ", প্রতিস্থাপন = "সেন্ট। পিটার্সবার্গ"] ,

    [ খুঁজুন = “সেন্ট. পিটার্সবার্গ", প্রতিস্থাপন = "সেন্ট। পিটার্সবার্গ"] ,

    [ খুঁজুন = "পিটার", প্রতিস্থাপন = "সেন্ট. পিটার্সবার্গ"] ,

ইত্যাদি।

}

পাওয়ার কোয়েরিতে নির্মিত এম ভাষার একটি বিশেষ ফাংশন ব্যবহার করে এই ধরনের রূপান্তর করা হয় - সারণী. এটি সরাসরি সূত্র বারে প্রয়োগ করতে, সেখানে ধাপ কোডে এই ফাংশনটি যোগ করুন উৎস.

ইহা ছিল:

পরে:

Table.ToRecords ফাংশন যোগ করার পরে, আমাদের টেবিলের চেহারা পরিবর্তন হবে - এটি রেকর্ডের একটি তালিকায় পরিণত হবে। পৃথক রেকর্ডের বিষয়বস্তু ভিউ ফলকের নীচে যে কোনও শব্দের পাশে ঘরের পটভূমিতে ক্লিক করে দেখা যেতে পারে। রেকর্ড (কিন্তু এক কথায় নয়!)

উপরেরটি ছাড়াও, আমাদের তৈরি করা তালিকাকে ক্যাশে (বাফার) করার জন্য আরও একটি স্ট্রোক যুক্ত করা বোধগম্য। এটি পাওয়ার ক্যোয়ারীকে আমাদের লুকআপ তালিকাকে একবার মেমরিতে লোড করতে বাধ্য করবে এবং যখন আমরা পরে এটিকে প্রতিস্থাপন করার জন্য অ্যাক্সেস করি তখন এটি পুনরায় গণনা করবে না। এটি করার জন্য, আমাদের সূত্রটি অন্য একটি ফাংশনে মোড়ানো - তালিকা.বাফার:

এই ধরনের ক্যাশিং গতিতে খুব লক্ষণীয় বৃদ্ধি দেবে (বেশ কয়েকবার!) প্রচুর পরিমাণে প্রাথমিক ডেটা সাফ করা হবে।

এটি হ্যান্ডবুকের প্রস্তুতি সম্পন্ন করে।

এটি ক্লিক করতে অবশেষ হোম - বন্ধ করুন এবং লোড করুন - বন্ধ করুন এবং লোড করুন... (হোম - বন্ধ করুন এবং লোড করুন - বন্ধ করুন এবং লোড করুন..), যেকোনো একটি নির্বাচন করুন শুধু একটি সংযোগ তৈরি করুন (শুধুমাত্র সংযোগ তৈরি করুন) এবং এক্সেল এ ফিরে যান।

ধাপ 2. ডেটা টেবিল লোড হচ্ছে

সবকিছু এখানে trite. রেফারেন্স বইয়ের সাথে আগের মতো, আমরা টেবিলের যে কোনও জায়গায় উঠে যাই, ট্যাবে ক্লিক করি উপাত্ত বোতাম টেবিল/রেঞ্জ থেকে এবং আমাদের টেবিল উপাত্ত পাওয়ার কোয়েরিতে প্রবেশ করে। স্বয়ংক্রিয়ভাবে যোগ করা পদক্ষেপ পরিবর্তিত প্রকার (পরিবর্তিত প্রকার) আপনি অপসারণ করতে পারেন:

এটির সাথে কোন বিশেষ প্রস্তুতিমূলক কাজ করার প্রয়োজন নেই এবং আমরা সবচেয়ে গুরুত্বপূর্ণ জিনিসটির দিকে এগিয়ে যাই।

ধাপ 3. List.Accumulate ফাংশন ব্যবহার করে প্রতিস্থাপন করুন

কমান্ডটি ব্যবহার করে আমাদের ডেটা টেবিলে একটি গণনা করা কলাম যোগ করা যাক একটি কলাম যোগ করা - কাস্টম কলাম (কলাম যোগ করুন — কাস্টম কলাম): এবং খোলা উইন্ডোতে যোগ করা কলামের নাম লিখুন (উদাহরণস্বরূপ, সংশোধন করা ঠিকানা) এবং আমাদের জাদু ফাংশন তালিকা. জমে:

এটি ক্লিক করতে অবশেষ OK - এবং আমরা প্রতিস্থাপনের সাথে একটি কলাম পাই:

মনে রাখবেন যে:

• যেহেতু পাওয়ার ক্যোয়ারী কেস সংবেদনশীল, তাই শেষ লাইনে কোন প্রতিস্থাপন করা হয়নি, কারণ ডিরেক্টরিতে আমাদের "SPb" আছে, "SPb" নয়।
• যদি সোর্স ডেটাতে একবারে প্রতিস্থাপনের জন্য বেশ কয়েকটি সাবস্ট্রিং থাকে (উদাহরণস্বরূপ, 7 তম লাইনে আপনাকে "S-Pb" এবং "প্রসপেক্টাস" উভয়ই প্রতিস্থাপন করতে হবে), তবে এটি কোনও সমস্যা তৈরি করে না (এর সূত্র দিয়ে প্রতিস্থাপনের বিপরীতে পূর্ববর্তী পদ্ধতি)।
• যদি উৎস পাঠে (9ম লাইন) প্রতিস্থাপন করার মতো কিছুই না থাকে, তাহলে কোনো ত্রুটি ঘটবে না (আবার, সূত্র দ্বারা প্রতিস্থাপনের বিপরীতে)।

যেমন একটি অনুরোধের গতি খুব, খুব শালীন. উদাহরণস্বরূপ, 5000 সারির আকার সহ প্রাথমিক ডেটার একটি টেবিলের জন্য, এই ক্যোয়ারীটি এক সেকেন্ডেরও কম সময়ে আপডেট করা হয়েছিল (বাফারিং ছাড়াই, প্রায় 3 সেকেন্ড!)

কিভাবে List.Accumulate ফাংশন কাজ করে

নীতিগতভাবে, এই নিবন্ধটি শেষ হতে পারে (আমার জন্য লিখতে এবং আপনার পড়ার জন্য)। আপনি যদি কেবল সক্ষম হতে চান না, তবে এটি "হুডের নীচে" কীভাবে কাজ করে তাও বুঝতে চান, তবে আপনাকে খরগোশের গর্তে আরও গভীরভাবে ডুব দিতে হবে এবং তালিকার সাথে মোকাবিলা করতে হবে। আমাদের জন্য কাজ.

এই ফাংশনের জন্য সিনট্যাক্স হল:

= তালিকা।তালিকা, বীজ, সঁচায়ক)

কোথায়

• তালিকা তালিকা যার উপাদান আমরা পুনরাবৃত্তি করছি. 
• বীজ - প্রারম্ভিক অবস্থা
• সঁচায়ক - একটি ফাংশন যা তালিকার পরবর্তী উপাদানে কিছু অপারেশন (গাণিতিক, পাঠ্য, ইত্যাদি) সঞ্চালন করে এবং একটি বিশেষ ভেরিয়েবলে প্রক্রিয়াকরণের ফলাফল জমা করে।
  

সাধারণভাবে, পাওয়ার কোয়েরিতে ফাংশন লেখার জন্য সিনট্যাক্স এইরকম দেখায়:

(argument1, argument2, … argumentN) => আর্গুমেন্ট সহ কিছু ক্রিয়া

উদাহরণস্বরূপ, সমষ্টি ফাংশনটি এইভাবে উপস্থাপন করা যেতে পারে:

(a, b) => a + b

List.Accumulate-এর জন্য, এই সঞ্চয়কারী ফাংশনের দুটি প্রয়োজনীয় আর্গুমেন্ট রয়েছে (তাদের যে কোনও নাম দেওয়া যেতে পারে, তবে সাধারণ নামগুলি হল অবস্থা и বর্তমান, এই ফাংশনের জন্য অফিসিয়াল সাহায্যের মতো, যেখানে:

• অবস্থা – একটি পরিবর্তনশীল যেখানে ফলাফল জমা হয় (এর প্রাথমিক মান উপরে উল্লিখিত একটি বীজ)
  
• বর্তমান - তালিকা থেকে পরবর্তী পুনরাবৃত্তি করা মান তালিকা
  

উদাহরণস্বরূপ, আসুন নিম্নলিখিত নির্মাণের যুক্তির পদক্ষেপগুলি দেখে নেওয়া যাক:

= তালিকা।{3, 2, 5}, 10, (state, current) => state + current)

এটি সর্বশেষ সঞ্চিত অবস্থা মান হল আমাদের তালিকা। ফলস্বরূপ ফাংশন এবং আউটপুট সংগ্রহ করুন:

আপনি যদি একটু কল্পনা করেন, তাহলে List.Accumulate ফাংশনটি ব্যবহার করে, আপনি অনুকরণ করতে পারেন, উদাহরণস্বরূপ, এক্সেল ফাংশন CONCATENATE (পাওয়ার কোয়েরিতে, এর অ্যানালগ বলা হয় পাঠ্য। একত্রিত করুন) অভিব্যক্তি ব্যবহার করে:

অথবা এমনকি সর্বোচ্চ মান অনুসন্ধান করুন (এক্সেলের MAX ফাংশনের অনুকরণ, যাকে পাওয়ার কোয়েরি বলা হয় তালিকা। সর্বোচ্চ):

যাইহোক, List.Accumulate-এর প্রধান বৈশিষ্ট্য হল শুধুমাত্র সাধারণ টেক্সট বা সাংখ্যিক তালিকাগুলিকে আর্গুমেন্ট হিসাবে প্রক্রিয়া করার ক্ষমতা নয়, বরং আরও জটিল বস্তু - উদাহরণস্বরূপ, তালিকা-থেকে-তালিকা বা তালিকা-থেকে-রেকর্ড (হ্যালো, ডিরেক্টরি!)

আমাদের সমস্যায় প্রতিস্থাপন সম্পাদনকারী নির্মাণটি আবার দেখা যাক:

তালিকা. জমা করা(নির্দেশিকা, [ঠিকানা], (state,current) => Text.Replace(state, current[Find], current[Replace]) )

সত্যিই এখানে কি ঘটছে না?

1. প্রাথমিক মান হিসাবে (বীজ) আমরা কলাম থেকে প্রথম আনাড়ি পাঠ গ্রহণ করি [ঠিকানা] আমাদের টেবিল: 199034, সেন্ট পিটার্সবার্গ, স্ট্র. বেরিঙ্গা, ঘ. 1
2. তারপর List.Accumulate পুনরাবৃত্ত তালিকার উপাদানগুলির উপর এক এক করে - হ্যান্ডবুক. এই তালিকার প্রতিটি উপাদান হল একটি রেকর্ড যা একজোড়া ক্ষেত্র নিয়ে গঠিত “কী খুঁজতে হবে – কী দিয়ে প্রতিস্থাপন করতে হবে” বা অন্য কথায়, ডিরেক্টরির পরবর্তী লাইন।
3. সঞ্চয়কারী ফাংশন একটি পরিবর্তনশীল মধ্যে রাখে অবস্থা প্রাথমিক মান (প্রথম ঠিকানা 199034, সেন্ট পিটার্সবার্গ, স্ট্র. বেরিঙ্গা, ঘ. 1) এবং এটিতে একটি সঞ্চয়কারী ফাংশন সম্পাদন করে – স্ট্যান্ডার্ড এম-ফাংশন ব্যবহার করে প্রতিস্থাপন অপারেশন পাঠ্য। প্রতিস্থাপন করুন (এক্সেলের সাবস্টিটিউট ফাংশনের অনুরূপ)। এর সিনট্যাক্স হল:

   Text.Replace (মূল পাঠ্য, আমরা যা খুঁজছি, আমরা কী দিয়ে প্রতিস্থাপন করছি)

   এবং এখানে আমাদের আছে:

• অবস্থা আমাদের নোংরা ঠিকানা, যা আছে অবস্থা (সেখান থেকে আসা বীজ)
  
• বর্তমান [অনুসন্ধান] - ক্ষেত্রের মান খুঁজতে তালিকার পরবর্তী পুনরাবৃত্তি এন্ট্রি থেকে নির্দেশিকা, যা ভেরিয়েবলের মধ্যে থাকে বর্তমান
• বর্তমান [প্রতিস্থাপন] - ক্ষেত্রের মান বিকল্প তালিকার পরবর্তী পুনরাবৃত্তি এন্ট্রি থেকে নির্দেশিকামিথ্যা বর্তমান

এইভাবে, প্রতিটি ঠিকানার জন্য, ডিরেক্টরির সমস্ত লাইনের গণনার একটি সম্পূর্ণ চক্র প্রতিবার চালানো হয়, [অনুসন্ধান] ক্ষেত্রের পাঠ্যটিকে [প্রতিস্থাপন] ক্ষেত্রের মান দিয়ে প্রতিস্থাপন করে।

আশা করি আপনি ধারণা পেয়েছেন 🙂

• সূত্র ব্যবহার করে একটি তালিকায় প্রচুর পরিমাণে পাঠ্য প্রতিস্থাপন করুন
  
• পাওয়ার কোয়েরিতে রেগুলার এক্সপ্রেশন (RegExp)