Parameterizing Data Paths ໃນ Power Query

ຖ້າທ່ານໄດ້ເລີ່ມໃຊ້ເຄື່ອງມືຂອງ Power Query add-in ໃນ Microsoft Excel ແລ້ວ, ຫຼັງຈາກນັ້ນໃນໄວໆນີ້ທ່ານຈະພົບກັບບັນຫາທີ່ມີຄວາມຊ່ຽວຊານສູງ, ແຕ່ເລື້ອຍໆແລະຫນ້າລໍາຄານທີ່ກ່ຽວຂ້ອງກັບການເຊື່ອມໂຍງຂໍ້ມູນແຫຼ່ງຢ່າງຕໍ່ເນື່ອງ. ໂດຍເນື້ອແທ້ແລ້ວຂອງບັນຫາແມ່ນວ່າຖ້າຢູ່ໃນຄໍາຖາມຂອງທ່ານທ່ານອ້າງເຖິງໄຟລ໌ພາຍນອກຫຼືໂຟນເດີ, ຫຼັງຈາກນັ້ນ Power Query hardcodes ເສັ້ນທາງຢ່າງແທ້ຈິງໃຫ້ພວກເຂົາຢູ່ໃນຂໍ້ຄວາມສອບຖາມ. ທຸກສິ່ງທຸກຢ່າງເຮັດວຽກໄດ້ດີໃນຄອມພິວເຕີຂອງທ່ານ, ແຕ່ຖ້າຫາກວ່າທ່ານຕັດສິນໃຈທີ່ຈະສົ່ງໄຟລ໌ທີ່ມີການຮ້ອງຂໍໃຫ້ເພື່ອນຮ່ວມງານຂອງທ່ານ, ຫຼັງຈາກນັ້ນພວກເຂົາເຈົ້າຈະຜິດຫວັງ, ເນື່ອງຈາກວ່າ. ພວກເຂົາມີເສັ້ນທາງທີ່ແຕກຕ່າງກັນໄປຫາຂໍ້ມູນແຫຼ່ງໃນຄອມພິວເຕີຂອງພວກເຂົາ, ແລະການສອບຖາມຂອງພວກເຮົາຈະບໍ່ເຮັດວຽກ.

ຈະເຮັດແນວໃດໃນສະຖານະການດັ່ງກ່າວ? ໃຫ້ເບິ່ງກໍລະນີນີ້ໃນລາຍລະອຽດເພີ່ມເຕີມດ້ວຍຕົວຢ່າງຕໍ່ໄປນີ້.

ການສ້າງບັນຫາ

ສົມມຸດວ່າພວກເຮົາມີຢູ່ໃນໂຟນເດີ E:ບົດລາຍງານການຂາຍ ນອນໄຟລ໌ 100 ອັນດັບຜະລິດຕະພັນ.xls, ເຊິ່ງເປັນການອັບໂຫລດຈາກຖານຂໍ້ມູນຂອງບໍລິສັດ ຫຼືລະບົບ ERP ຂອງພວກເຮົາ (1C, SAP, ແລະອື່ນໆ) ໄຟລ໌ນີ້ມີຂໍ້ມູນກ່ຽວກັບລາຍການສິນຄ້າທີ່ນິຍົມຫຼາຍທີ່ສຸດ ແລະມີລັກສະນະພາຍໃນ:

ມັນອາດຈະເປັນທີ່ຈະແຈ້ງທັນທີທີ່ມັນເກືອບເປັນໄປບໍ່ໄດ້ທີ່ຈະເຮັດວຽກກັບມັນໃນ Excel ໃນຮູບແບບນີ້: ແຖວຫວ່າງເປົ່າຜ່ານຫນຶ່ງທີ່ມີຂໍ້ມູນ, ຈຸລັງທີ່ຖືກລວມ, ຖັນພິເສດ, ຫົວຫຼາຍລະດັບ, ແລະອື່ນໆຈະແຊກແຊງ.

ດັ່ງນັ້ນ, ຕໍ່ໄປກັບໄຟລ໌ນີ້ຢູ່ໃນໂຟນເດີດຽວກັນ, ພວກເຮົາສ້າງໄຟລ໌ໃຫມ່ອື່ນ Handler.xlsx, ໃນທີ່ພວກເຮົາຈະສ້າງແບບສອບຖາມ Power Query ທີ່ຈະໂຫລດຂໍ້ມູນທີ່ຫນ້າກຽດຈາກໄຟລ໌ອັບໂຫລດແຫຼ່ງ 100 ອັນດັບຜະລິດຕະພັນ.xls, ແລະ​ຈັດ​ໃຫ້​ເຂົາ​ເຈົ້າ​ຢູ່​ໃນ​ຄໍາ​ສັ່ງ​:

ເຮັດການຮ້ອງຂໍກັບໄຟລ໌ພາຍນອກ

ກຳລັງເປີດໄຟລ໌ Handler.xlsx, ເລືອກຢູ່ໃນແຖບ ຂໍ້ມູນ ຄໍາສັ່ງ ເອົາຂໍ້ມູນ - ຈາກເອກະສານ - ຈາກ Excel Workbook (ຂໍ້ມູນ — ໄດ້​ຮັບ​ຂໍ້​ມູນ — ຈາກ​ໄຟລ​໌ — ຈາກ Excel​), ຫຼັງຈາກນັ້ນໃຫ້ລະບຸສະຖານທີ່ຂອງໄຟລ໌ແຫຼ່ງແລະແຜ່ນທີ່ພວກເຮົາຕ້ອງການ. ຂໍ້ມູນທີ່ເລືອກຈະຖືກໂຫລດໃສ່ຕົວແກ້ໄຂ Power Query:

ໃຫ້ພວກເຮົາກັບຄືນສູ່ສະພາບປົກກະຕິ:

1. ລົບເສັ້ນເປົ່າດ້ວຍ ຫນ້າທໍາອິດ — ລຶບສາຍ — ລຶບເສັ້ນເປົ່າ (ຫນ້າທໍາອິດ — Remove Rows — Remove Empty Rows ).
2. ລຶບ 4 ແຖວເທິງທີ່ບໍ່ຈໍາເປັນຜ່ານ ຫນ້າທໍາອິດ — ລຶບແຖວ — ລຶບແຖວເທິງ (ຫນ້າທໍາອິດ — Remove Rows — Remove Top Rows).
3. ຍົກແຖວທໍາອິດໃສ່ຫົວຕາຕະລາງດ້ວຍປຸ່ມ ໃຊ້ແຖວທຳອິດເປັນສ່ວນຫົວ ແຖບ ຫນ້າທໍາອິດ (ຫນ້າທໍາອິດ - ໃຊ້ແຖວທໍາອິດເປັນສ່ວນຫົວ).
4. ແຍກບົດຄວາມຫ້າຕົວເລກອອກຈາກຊື່ຜະລິດຕະພັນໃນຖັນທີສອງໂດຍໃຊ້ຄໍາສັ່ງ ແຍກຖັນ ແຖບ ການຫັນເປັນ (ການຫັນປ່ຽນ — ຖັນແຍກ).
5. ລຶບຖັນທີ່ບໍ່ຈຳເປັນອອກ ແລະປ່ຽນຊື່ຫົວຂໍ້ຂອງຖັນທີ່ຍັງເຫຼືອເພື່ອການເບິ່ງເຫັນທີ່ດີຂຶ້ນ.

ດັ່ງນັ້ນ, ພວກເຮົາຄວນຈະໄດ້ຮັບດັ່ງຕໍ່ໄປນີ້, ຮູບພາບທີ່ມີຄວາມສຸກຫຼາຍ:

ມັນຍັງຄົງທີ່ຈະອັບໂຫລດຕາຕະລາງ ennobled ນີ້ກັບຄືນໄປບ່ອນແຜ່ນໃນເອກະສານຂອງພວກເຮົາ Handler.xlsx ທີມງານ ປິດແລະດາວໂຫລດ (ຫນ້າທໍາອິດ — ປິດແລະໂຫຼດ) ແຖບ ຫນ້າທໍາອິດ:

ຊອກຫາເສັ້ນທາງໄປຫາໄຟລ໌ໃນຄໍາຮ້ອງຂໍ

ຕອນນີ້ໃຫ້ເບິ່ງວ່າແບບສອບຖາມຂອງພວກເຮົາເບິ່ງຄືແນວໃດ "ພາຍໃຕ້ຜ້າຄຸມ", ໃນພາສາພາຍໃນທີ່ສ້າງຂຶ້ນໃນ Power Query ທີ່ມີຊື່ຫຍໍ້ "M". ເພື່ອເຮັດສິ່ງນີ້, ໃຫ້ກັບຄືນໄປຫາຄໍາຖາມຂອງພວກເຮົາໂດຍການຄລິກສອງຄັ້ງໃສ່ມັນຢູ່ໃນແຖບດ້ານຂວາ ການຮ້ອງຂໍແລະການເຊື່ອມຕໍ່ ແລະຢູ່ໃນແຖບ ການທົບທວນຄືນ ເລືອກ ບັນນາທິການຂັ້ນສູງ (ເບິ່ງ - ບັນນາທິການຂັ້ນສູງ):

ຢູ່ໃນປ່ອງຢ້ຽມທີ່ເປີດຂຶ້ນ, ແຖວທີສອງຈະເປີດເຜີຍເສັ້ນທາງທີ່ມີລະຫັດຍາກໄປຫາໄຟລ໌ອັບໂຫລດຕົ້ນສະບັບຂອງພວກເຮົາ. ຖ້າພວກເຮົາສາມາດປ່ຽນແທນຂໍ້ຄວາມນີ້ດ້ວຍພາລາມິເຕີ, ຕົວແປ, ຫຼືການເຊື່ອມຕໍ່ໄປຫາຕາລາງແຜ່ນ Excel ທີ່ເສັ້ນທາງນີ້ຖືກຂຽນໄວ້ກ່ອນ, ຫຼັງຈາກນັ້ນພວກເຮົາສາມາດປ່ຽນມັນໄດ້ງ່າຍໆໃນພາຍຫຼັງ.

ເພີ່ມຕາຕະລາງອັດສະລິຍະທີ່ມີເສັ້ນທາງໄຟລ໌

ໃຫ້ປິດ Power Query ດຽວນີ້ ແລະກັບຄືນໄປຫາໄຟລ໌ຂອງພວກເຮົາ Handler.xlsx. ໃຫ້ເພີ່ມແຜ່ນເປົ່າໃຫມ່ແລະສ້າງຕາຕະລາງ "smart" ຂະຫນາດນ້ອຍໃສ່ມັນ, ໃນຫ້ອງດຽວທີ່ເສັ້ນທາງເຕັມໄປຫາໄຟລ໌ຂໍ້ມູນແຫຼ່ງຂອງພວກເຮົາຈະຖືກຂຽນ:

ເພື່ອສ້າງຕາຕະລາງອັດສະລິຍະຈາກລະດັບປົກກະຕິ, ທ່ານສາມາດນໍາໃຊ້ປຸ່ມລັດແປ້ນພິມ Ctrl+T ຫຼືປຸ່ມ ຈັດຮູບແບບເປັນຕາຕະລາງ ແຖບ ຫນ້າທໍາອິດ (ຫນ້າທໍາອິດ — Format as ຕາ​ຕະ​ລາງ​). ຫົວຂໍ້ຖັນ (ເຊລ A1) ສາມາດເປັນອັນໃດກໍໄດ້. ໃຫ້ສັງເກດວ່າສໍາລັບຄວາມຊັດເຈນຂ້າພະເຈົ້າໄດ້ໃຫ້ຕາຕະລາງຊື່ ຕົວກໍານົດການ ແຖບ ຜູ້ກໍ່ສ້າງ (ອອກ​ແບບ).

ການຄັດລອກເສັ້ນທາງຈາກ Explorer ຫຼືແມ້ກະທັ້ງເຂົ້າໄປໃນມັນດ້ວຍຕົນເອງ, ແນ່ນອນ, ບໍ່ແມ່ນການຍາກ, ແຕ່ມັນດີທີ່ສຸດທີ່ຈະຫຼຸດຜ່ອນປັດໃຈຂອງມະນຸດແລະກໍານົດເສັ້ນທາງ, ຖ້າເປັນໄປໄດ້, ອັດຕະໂນມັດ. ນີ້ສາມາດຖືກປະຕິບັດໂດຍໃຊ້ຟັງຊັນ Excel ມາດຕະຖານ ເຊລ (ເຊລ), ເຊິ່ງສາມາດໃຫ້ຂໍ້ມູນທີ່ເປັນປະໂຫຍດຫຼາຍຢ່າງກ່ຽວກັບເຊລທີ່ລະບຸໄວ້ເປັນການໂຕ້ຖຽງ – ລວມທັງເສັ້ນທາງໄປຫາໄຟລ໌ປັດຈຸບັນ:

ຖ້າພວກເຮົາສົມມຸດວ່າໄຟລ໌ຂໍ້ມູນຕົ້ນສະບັບສະເຫມີຢູ່ໃນໂຟນເດີດຽວກັນກັບໂປເຊດເຊີຂອງພວກເຮົາ, ເສັ້ນທາງທີ່ພວກເຮົາຕ້ອງການສາມາດຖືກສ້າງຂື້ນໂດຍສູດຕໍ່ໄປນີ້:

=LEFT(CELL(“filename”);FIND(“[“;CELL(“filename”))-1)&”100 products.xls” ອັນດັບຕົ້ນໆ

ຫຼື​ໃນ​ສະ​ບັບ​ພາ​ສາ​ອັງ​ກິດ​:

=LEFT(CELL(«ຊື່ໄຟລ໌»);FIND(«[«;CELL(«ຊື່ໄຟລ໌»))-1)&»Топ-100 товаров.xls»

… ໜ້າທີ່ຢູ່ໃສ LEVSIMV (ຊ້າຍ) ເອົາຂໍ້ຄວາມຈາກການເຊື່ອມຕໍ່ເຕັມໄປຫາວົງເລັບສີ່ຫລ່ຽມເປີດ (ເຊັ່ນ: ເສັ້ນທາງໄປຫາໂຟເດີປະຈຸບັນ), ແລະຫຼັງຈາກນັ້ນຊື່ແລະນາມສະກຸນຂອງໄຟລ໌ຂໍ້ມູນແຫຼ່ງຂອງພວກເຮົາຖືກຕິດໃສ່ມັນ.

Parameterize ເສັ້ນທາງໃນການສອບຖາມ

ການສໍາພັດສຸດທ້າຍແລະສໍາຄັນທີ່ສຸດຍັງຄົງຢູ່ - ເພື່ອຂຽນເສັ້ນທາງໄປຫາໄຟລ໌ແຫຼ່ງໃນຄໍາຮ້ອງຂໍ 100 ອັນດັບຜະລິດຕະພັນ.xlsໂດຍອ້າງອີງໃສ່ຕາລາງ A2 ຂອງຕາຕະລາງ "smart" ທີ່ສ້າງຂຶ້ນຂອງພວກເຮົາ ຕົວກໍານົດການ.

ເພື່ອເຮັດສິ່ງນີ້, ໃຫ້ກັບຄືນໄປຫາຄໍາຖາມ Power Query ແລະເປີດມັນອີກເທື່ອຫນຶ່ງ ບັນນາທິການຂັ້ນສູງ ແຖບ ການທົບທວນຄືນ (ເບິ່ງ - ບັນນາທິການຂັ້ນສູງ). ແທນທີ່ຈະເປັນເສັ້ນທາງຂໍ້ຄວາມໃນວົງຢືມ "E: ບົດລາຍງານການຂາຍຍອດນິຍົມ 100 ຜະລິດຕະພັນ.xlsx" ຂໍ​ໃຫ້​ນໍາ​ສະ​ເຫນີ​ໂຄງ​ສ້າງ​ດັ່ງ​ຕໍ່​ໄປ​ນີ້​:

Excel.CurrentWorkbook(){[name=”ການຕັ້ງຄ່າ”]}[ເນື້ອຫາ]0 {}[ເສັ້ນທາງໄປຫາຂໍ້ມູນແຫຼ່ງ]

ມາ​ເບິ່ງ​ວ່າ​ມັນ​ປະ​ກອບ​ດ້ວຍ​ຫຍັງ​:

• Excel.CurrentWorkbook() ແມ່ນຫນ້າທີ່ຂອງພາສາ M ສໍາລັບການເຂົ້າເຖິງເນື້ອໃນຂອງໄຟລ໌ປະຈຸບັນ
• {[name=”ການຕັ້ງຄ່າ”]}[ເນື້ອຫາ] - ນີ້​ແມ່ນ​ຕົວ​ກໍາ​ນົດ​ການ​ປັບ​ປຸງ​ຫນ້າ​ທີ່​ທີ່​ຜ່ານ​ມາ​, ສະ​ແດງ​ໃຫ້​ເຫັນ​ວ່າ​ພວກ​ເຮົາ​ຕ້ອງ​ການ​ທີ່​ຈະ​ໄດ້​ຮັບ​ເນື້ອ​ໃນ​ຂອງ​ຕາ​ຕະ​ລາງ "smart​" ໄດ້​. ຕົວກໍານົດການ
• [ເສັ້ນທາງໄປຫາຂໍ້ມູນແຫຼ່ງ] ແມ່ນຊື່ຂອງຖັນໃນຕາຕະລາງ ຕົວກໍານົດການທີ່ພວກເຮົາອ້າງເຖິງ
  
• 0 {} ແມ່ນຕົວເລກແຖວໃນຕາຕະລາງ ຕົວກໍານົດການຈາກທີ່ພວກເຮົາຕ້ອງການເອົາຂໍ້ມູນ. ໝວກບໍ່ນັບ ແລະຕົວເລກເລີ່ມຕົ້ນຈາກສູນ, ບໍ່ແມ່ນຈາກໜຶ່ງ.

ນັ້ນແມ່ນທັງຫມົດ, ໃນຄວາມເປັນຈິງ.

ມັນຍັງຄົງໃຫ້ຄລິກໃສ່ ສໍາ​ເລັດ​ຮູບ ແລະກວດເບິ່ງວ່າຄໍາຮ້ອງຂໍຂອງພວກເຮົາເຮັດວຽກແນວໃດ. ໃນປັດຈຸບັນ, ເມື່ອສົ່ງໂຟນເດີທັງຫມົດທີ່ມີທັງສອງໄຟລ໌ພາຍໃນກັບ PC ອື່ນ, ການຮ້ອງຂໍຈະຍັງຄົງດໍາເນີນການແລະກໍານົດເສັ້ນທາງໄປຫາຂໍ້ມູນອັດຕະໂນມັດ.

• Power Query ແມ່ນຫຍັງ ແລະເປັນຫຍັງມັນຈຶ່ງຕ້ອງການເມື່ອເຮັດວຽກໃນ Microsoft Excel
• ວິທີການນໍາເຂົ້າ snippet ຂໍ້ຄວາມລອຍເຂົ້າໄປໃນ Power Query
• ການອອກແບບໃຫມ່ XNUMXD Crosstab ກັບຕາຕະລາງແປດ້ວຍການສອບຖາມພະລັງງານ