পাওয়ার কোয়েরিতে ডেটা পাথ প্যারামিটারাইজ করা

আপনি যদি ইতিমধ্যেই মাইক্রোসফ্ট এক্সেলে বিনামূল্যে পাওয়ার কোয়েরি অ্যাড-ইন-এর সরঞ্জামগুলি ব্যবহার করা শুরু করে থাকেন, তাহলে খুব শীঘ্রই আপনি একটি অত্যন্ত বিশেষায়িত, কিন্তু খুব ঘন ঘন এবং বিরক্তিকর সমস্যার সম্মুখীন হবেন যা উৎস ডেটার ক্রমাগত লিঙ্কগুলি ভাঙার সাথে যুক্ত। সমস্যার সারমর্ম হল যে আপনার ক্যোয়ারীতে আপনি যদি এক্সটার্নাল ফাইল বা ফোল্ডার উল্লেখ করেন, তাহলে পাওয়ার কোয়েরি হার্ডকোড ক্যোয়ারী টেক্সটে তাদের জন্য নিখুঁত পথ তৈরি করে। আপনার কম্পিউটারে সবকিছু ঠিকঠাক কাজ করে, তবে আপনি যদি আপনার সহকর্মীদের কাছে একটি অনুরোধ সহ একটি ফাইল পাঠানোর সিদ্ধান্ত নেন, তবে তারা হতাশ হবেন, কারণ। তাদের কম্পিউটারে সোর্স ডেটার জন্য তাদের একটি ভিন্ন পথ রয়েছে এবং আমাদের ক্যোয়ারী কাজ করবে না।

এমন পরিস্থিতিতে কী করবেন? আসুন নিম্নলিখিত উদাহরণের সাথে আরও বিস্তারিতভাবে এই কেসটি দেখি।

সমস্যা প্রণয়ন

ধরুন আমাদের ফোল্ডারে আছে E:বিক্রয় রিপোর্ট ফাইল মিথ্যা সেরা 100টি পণ্য.xls, যা আমাদের কর্পোরেট ডাটাবেস বা ERP সিস্টেম (1C, SAP, ইত্যাদি) থেকে আপলোড করা হয়েছে

এটি সম্ভবত ব্যাট থেকে স্পষ্ট যে এটির সাথে এক্সেলে এই ফর্মে কাজ করা প্রায় অসম্ভব: ডেটা সহ একের মাধ্যমে খালি সারি, একত্রিত ঘর, অতিরিক্ত কলাম, একটি মাল্টি-লেভেল হেডার ইত্যাদি হস্তক্ষেপ করবে।

অতএব, একই ফোল্ডারে এই ফাইলের পাশে, আমরা আরেকটি নতুন ফাইল তৈরি করি হ্যান্ডলার.xlsx, যাতে আমরা একটি পাওয়ার কোয়েরি কোয়েরি তৈরি করব যা উৎস আপলোড ফাইল থেকে কুৎসিত ডেটা লোড করবে সেরা 100টি পণ্য.xls, এবং তাদের ক্রমানুসারে রাখুন:

একটি বহিরাগত ফাইল একটি অনুরোধ করা

ফাইল খুলছে হ্যান্ডলার.xlsx, ট্যাবে নির্বাচন করুন উপাত্ত আদেশ ডেটা পান - ফাইল থেকে - এক্সেল ওয়ার্কবুক থেকে (ডেটা — ডেটা পান — ফাইল থেকে — এক্সেল থেকে), তারপর উৎস ফাইলের অবস্থান এবং আমাদের প্রয়োজনীয় শীট উল্লেখ করুন। নির্বাচিত ডেটা পাওয়ার কোয়েরি সম্পাদকে লোড করা হবে:

আসুন তাদের স্বাভাবিক অবস্থায় ফিরিয়ে আনি:

1. সঙ্গে খালি লাইন মুছুন হোম — লাইন মুছুন — খালি লাইন মুছুন (হোম - সারিগুলি সরান - খালি সারিগুলি সরান).
2. এর মাধ্যমে অপ্রয়োজনীয় শীর্ষ 4 লাইন মুছুন হোম — সারি মুছুন — শীর্ষ সারি মুছুন (হোম - সারিগুলি সরান - শীর্ষ সারিগুলি সরান).
3. বোতামটি দিয়ে প্রথম সারিটি টেবিলের শিরোনামে তুলুন হেডার হিসেবে প্রথম লাইন ব্যবহার করুন ট্যাব হোম (হোম - হেডার হিসাবে প্রথম সারি ব্যবহার করুন).
4. কমান্ড ব্যবহার করে দ্বিতীয় কলামে পণ্যের নাম থেকে পাঁচ অঙ্কের নিবন্ধটি আলাদা করুন বিভক্ত কলাম ট্যাব রুপান্তর (রূপান্তর — বিভক্ত কলাম).
5. অপ্রয়োজনীয় কলামগুলি মুছুন এবং আরও ভাল দৃশ্যমানতার জন্য অবশিষ্টগুলির শিরোনামগুলির নাম পরিবর্তন করুন৷

ফলস্বরূপ, আমাদের নিম্নলিখিত, অনেক বেশি মনোরম ছবি পাওয়া উচিত:

আমাদের ফাইলের শীটে এই এননোবলড টেবিলটি আবার আপলোড করা বাকি আছে হ্যান্ডলার.xlsx দলটি বন্ধ করুন এবং ডাউনলোড করুন (বাড়ি — বন্ধ করুন এবং লোড করুন) ট্যাব হোম:

একটি অনুরোধে একটি ফাইলের পথ খোঁজা

এখন দেখা যাক আমাদের ক্যোয়ারীটি "হুডের নীচে" কেমন দেখায়, সংক্ষিপ্ত নাম "M" সহ পাওয়ার কোয়েরিতে অন্তর্নির্মিত অভ্যন্তরীণ ভাষায়। এটি করার জন্য, ডান ফলকে এটিতে ডাবল ক্লিক করে আমাদের ক্যোয়ারীতে ফিরে যান অনুরোধ এবং সংযোগ এবং ট্যাবে পর্যালোচনা পছন্দ অ্যাডভান্সড এডিটর (দেখুন — অ্যাডভান্সড এডিটর):

যে উইন্ডোটি খোলে, দ্বিতীয় লাইনটি অবিলম্বে আমাদের আসল আপলোড ফাইলের একটি হার্ড-কোডেড পথ প্রকাশ করে। যদি আমরা এই টেক্সট স্ট্রিংটিকে একটি প্যারামিটার, ভেরিয়েবল বা এক্সেল শীট সেলের একটি লিঙ্ক দিয়ে প্রতিস্থাপন করতে পারি যেখানে এই পথটি আগে থেকে লেখা আছে, তাহলে আমরা পরে সহজেই এটি পরিবর্তন করতে পারি।

একটি ফাইল পাথ সহ একটি স্মার্ট টেবিল যোগ করুন

এখন পাওয়ার কোয়েরি বন্ধ করে আমাদের ফাইলে ফিরে আসুন হ্যান্ডলার.xlsx. আসুন একটি নতুন খালি শীট যোগ করি এবং এটিতে একটি ছোট "স্মার্ট" টেবিল তৈরি করি, যার একমাত্র কক্ষে আমাদের উত্স ডেটা ফাইলের সম্পূর্ণ পথটি লেখা হবে:

নিয়মিত পরিসর থেকে একটি স্মার্ট টেবিল তৈরি করতে, আপনি কীবোর্ড শর্টকাট ব্যবহার করতে পারেন জন্য ctrl+T বা বোতাম একটি টেবিল হিসাবে বিন্যাস ট্যাব হোম (হোম - টেবিল হিসাবে ফর্ম্যাট). কলাম শিরোনাম (সেল A1) একেবারে কিছু হতে পারে। এছাড়াও নোট করুন যে স্পষ্টতার জন্য আমি টেবিলের একটি নাম দিয়েছি পরামিতি ট্যাব রচয়িতা (নকশা).

এক্সপ্লোরার থেকে একটি পাথ অনুলিপি করা বা এমনকি ম্যানুয়ালি প্রবেশ করা অবশ্যই, বিশেষত কঠিন নয়, তবে মানব ফ্যাক্টরকে ছোট করা এবং সম্ভব হলে স্বয়ংক্রিয়ভাবে পথ নির্ধারণ করা ভাল। এটি স্ট্যান্ডার্ড এক্সেল ওয়ার্কশীট ফাংশন ব্যবহার করে বাস্তবায়ন করা যেতে পারে সেল (সেল), যা একটি আর্গুমেন্ট হিসাবে নির্দিষ্ট করা সেল সম্পর্কে দরকারী তথ্যের একটি গুচ্ছ দিতে পারে - বর্তমান ফাইলের পথ সহ:

যদি আমরা ধরে নিই যে সোর্স ডেটা ফাইলটি সর্বদা আমাদের প্রসেসরের মতো একই ফোল্ডারে থাকে, তাহলে আমাদের প্রয়োজনীয় পথটি নিম্নলিখিত সূত্র দ্বারা গঠিত হতে পারে:

=LEFT(CELL("ফাইলের নাম");FIND("[";CELL("ফাইলের নাম"))-1)&"Top 100 products.xls"

অথবা ইংরেজি সংস্করণে:

=LEFT(CELL(«ফাইলের নাম»);FIND(«[«;CELL(«ফাইলের নাম»))-1)&»Топ-100 товаров.xls»

… ফাংশন কোথায় LEVSIMV (বাম) পুরো লিঙ্ক থেকে শুরুর বর্গাকার বন্ধনী (অর্থাৎ বর্তমান ফোল্ডারের পথ) পর্যন্ত পাঠ্যের একটি অংশ নেয় এবং তারপরে আমাদের উত্স ডেটা ফাইলের নাম এবং এক্সটেনশন এটিতে আটকে থাকে।

ক্যোয়ারীতে পাথ প্যারামিটারাইজ করুন

শেষ এবং সবচেয়ে গুরুত্বপূর্ণ স্পর্শ বাকি - অনুরোধে সোর্স ফাইলের পাথ লিখতে সেরা 100টি পণ্য.xls, আমাদের তৈরি "স্মার্ট" টেবিলের সেল A2 উল্লেখ করে পরামিতি.

এটি করার জন্য, আসুন Power Query ক্যোয়ারীতে ফিরে যাই এবং এটি আবার খুলি অ্যাডভান্সড এডিটর ট্যাব পর্যালোচনা (দেখুন — অ্যাডভান্সড এডিটর). উদ্ধৃতিগুলিতে একটি পাঠ্য স্ট্রিং-পাথের পরিবর্তে "ই:বিক্রয় প্রতিবেদন শীর্ষ 100 পণ্য.xlsx" আসুন নিম্নলিখিত কাঠামোটি প্রবর্তন করি:

Excel.CurrentWorkbook(){[নাম="সেটিংস"] [সামগ্রী]৭ {}[উৎস তথ্যের পথ]

আসুন দেখি এতে কী রয়েছে:

• Excel.CurrentWorkbook() বর্তমান ফাইলের বিষয়বস্তু অ্যাক্সেস করার জন্য M ভাষার একটি ফাংশন
• {[নাম="সেটিংস"] [সামগ্রী] - এটি পূর্ববর্তী ফাংশনের একটি পরিমার্জন পরামিতি, যা নির্দেশ করে যে আমরা "স্মার্ট" টেবিলের বিষয়বস্তু পেতে চাই পরামিতি
• [উৎস তথ্যের পথ] টেবিলের কলামের নাম পরামিতিযা আমরা উল্লেখ করি
  
• ৭ {} টেবিলের সারি সংখ্যা পরামিতিযেখান থেকে আমরা ডেটা নিতে চাই। ক্যাপটি গণনা করে না এবং সংখ্যায়ন শূন্য থেকে শুরু হয়, একটি থেকে নয়।

যে সব, আসলে.

এটি ক্লিক করতে অবশেষ শেষ এবং আমাদের অনুরোধ কিভাবে কাজ করে তা পরীক্ষা করুন। এখন, অন্য পিসিতে উভয় ফাইল সহ সম্পূর্ণ ফোল্ডার পাঠানোর সময়, অনুরোধটি কার্যকর থাকবে এবং স্বয়ংক্রিয়ভাবে ডেটার পথ নির্ধারণ করবে।

• পাওয়ার কোয়েরি কী এবং মাইক্রোসফ্ট এক্সেলে কাজ করার সময় কেন এটি প্রয়োজন
• পাওয়ার কোয়েরিতে একটি ভাসমান পাঠ্য স্নিপেট কীভাবে আমদানি করবেন
• পাওয়ার কোয়েরি সহ একটি ফ্ল্যাট টেবিলে একটি XNUMXD ক্রসট্যাব পুনরায় ডিজাইন করা