Power Query-ում տվյալների ուղիների պարամետրավորում

Եթե ​​արդեն սկսել եք օգտագործել Power Query հավելվածի գործիքները Microsoft Excel-ում, ապա շատ շուտով դուք կհանդիպեք մեկ խիստ մասնագիտացված, բայց շատ հաճախակի և զայրացնող խնդրի՝ կապված աղբյուրի տվյալների անընդհատ հղումները կոտրելու հետ: Խնդրի էությունը կայանում է նրանում, որ եթե ձեր հարցումում դուք վերաբերում եք արտաքին ֆայլերին կամ թղթապանակներին, ապա Power Query կոշտ կոդերը դրանց բացարձակ ուղին է հարցման տեքստում: Ձեր համակարգչում ամեն ինչ լավ է աշխատում, բայց եթե որոշեք խնդրանքով ֆայլ ուղարկել ձեր գործընկերներին, ապա նրանք կհիասթափվեն, քանի որ. նրանք ունեն այլ ճանապարհ դեպի աղբյուրի տվյալները իրենց համակարգչի վրա, և մեր հարցումը չի աշխատի:

Ի՞նչ անել նման իրավիճակում: Այս դեպքին ավելի մանրամասն նայենք հետևյալ օրինակով.

Խնդրի ձևակերպում

Ենթադրենք, մենք ունենք թղթապանակում E:Վաճառքների հաշվետվություններ ընկած է ֆայլը Լավագույն 100 ապրանքներ.xls, որը վերբեռնված է մեր կորպորատիվ տվյալների բազայից կամ ERP համակարգից (1C, SAP և այլն): Այս ֆայլը պարունակում է տեղեկատվություն ամենահայտնի ապրանքների մասին և ներսում այսպիսի տեսք ունի.

Հավանաբար, անմիջապես պարզ է, որ Excel-ում դրա հետ աշխատելն այս ձևով գրեթե անհնար է. կխանգարեն դատարկ տողերը, որոնք պարունակում են տվյալներ, միավորված բջիջներ, լրացուցիչ սյունակներ, բազմամակարդակ վերնագիր և այլն:

Հետեւաբար, նույն թղթապանակում գտնվող այս ֆայլի կողքին մենք ստեղծում ենք մեկ այլ նոր ֆայլ Handler.xlsx, որում մենք կստեղծենք Power Query հարցում, որը կբեռնի տգեղ տվյալներ աղբյուրի վերբեռնման ֆայլից Լավագույն 100 ապրանքներ.xls, և դրանք կարգի բերեք.

Արտաքին ֆայլին հարցում կատարելը

Ֆայլի բացում Handler.xlsx, ընտրեք ներդիրում Ամսաթիվ Հրաման Ստացեք տվյալներ – ֆայլից – Excel աշխատանքային գրքույկից (Տվյալներ — Ստացեք տվյալներ — Ֆայլից — Excel-ից), ապա նշեք սկզբնաղբյուր ֆայլի և մեզ անհրաժեշտ թերթիկի գտնվելու վայրը։ Ընտրված տվյալները կբեռնվեն Power Query խմբագրիչում՝

Եկեք դրանք վերադարձնենք նորմալ վիճակի.

1. Ջնջել դատարկ տողերը Գլխավոր — Ջնջել տողերը — Ջնջել դատարկ տողերը (Տուն — Հեռացնել տողերը — Հեռացնել դատարկ տողերը).
2. Ջնջել ավելորդ վերին 4 տողերը Գլխավոր — Ջնջել տողերը — Ջնջել վերին տողերը (Տուն — Հեռացնել տողերը — Հեռացնել վերին տողերը).
3. Կոճակով առաջին տողը բարձրացրեք աղյուսակի վերնագրի վրա Օգտագործեք առաջին տողը որպես վերնագիր ականջակալ Գլխավոր (Տուն — Օգտագործել առաջին տողը որպես վերնագիր).
4. Հրամանի միջոցով առանձնացրեք հինգանիշ հոդվածը երկրորդ սյունակի ապրանքի անունից պառակտված սյունակ ականջակալ Փոխակերպում (Փոխակերպում — Պառակտված սյունակ).
5. Ջնջեք ավելորդ սյունակները և վերանվանեք մնացածների վերնագրերը՝ ավելի լավ տեսանելիության համար:

Արդյունքում մենք պետք է ստանանք հետևյալ, շատ ավելի հաճելի պատկերը.

Մնում է վերբեռնել այս վեհացված աղյուսակը մեր ֆայլի թերթիկում Handler.xlsx թիմը փակիր և ներբեռնիր (Տուն — Փակել & Բեռնել) ականջակալ Գլխավոր:

Հարցման մեջ ֆայլի ուղի գտնելը

Հիմա եկեք տեսնենք, թե ինչպես է մեր հարցումը նայում «կապակի տակ», ներքին լեզվով, որը ներկառուցված է Power Query-ում «M» հակիրճ անունով: Դա անելու համար վերադարձեք մեր հարցումին՝ կրկնակի սեղմելով դրա վրա աջ վահանակում Հարցումներ և կապեր և ներդիրի վրա տեսություն ընտրել Ընդլայնված խմբագիր (Դիտել — Ընդլայնված խմբագիր):

Բացվող պատուհանում երկրորդ տողն անմիջապես բացահայտում է կոշտ կոդավորված ուղին դեպի մեր սկզբնական վերբեռնման ֆայլը: Եթե ​​մենք կարողանանք փոխարինել այս տեքստային տողը պարամետրով, փոփոխականով կամ հղումով դեպի Excel թերթի բջիջ, որտեղ այս ուղին նախապես գրված է, ապա մենք կարող ենք հեշտությամբ փոխել այն ավելի ուշ:

Ավելացնել խելացի աղյուսակ՝ ֆայլի ուղով

Եկեք փակենք Power Query-ն առայժմ և վերադառնանք մեր ֆայլին Handler.xlsx. Եկեք նոր դատարկ թերթիկ ավելացնենք և դրա վրա կազմենք փոքրիկ «խելացի» աղյուսակ, որի միակ բջիջում կգրվի մեր աղբյուրի տվյալների ֆայլի ամբողջական ուղին.

Սովորական միջակայքից խելացի աղյուսակ ստեղծելու համար կարող եք օգտագործել ստեղնաշարի դյուրանցումը Ctrl+T կամ կոճակ Ձևաչափեք որպես աղյուսակ ականջակալ Գլխավոր (Տուն — Ձևաչափել որպես աղյուսակ). Սյունակի վերնագիրը (A1 բջիջ) կարող է լինել բացարձակապես ցանկացած: Նաև նշեք, որ պարզության համար ես աղյուսակին տվել եմ անվանում Պարամետրեր ականջակալ շինարար (Դիզայն).

Explorer-ից ուղի պատճենելը կամ նույնիսկ ձեռքով մուտքագրելը, իհարկե, առանձնապես դժվար չէ, բայց լավագույնն է նվազագույնի հասցնել մարդկային գործոնը և, հնարավորության դեպքում, ավտոմատ կերպով որոշել ճանապարհը: Սա կարող է իրականացվել Excel-ի աշխատանքային թերթիկի ստանդարտ ֆունկցիայի միջոցով CELL (ԲՋՋ), որը կարող է տալ մի շարք օգտակար տեղեկություններ բջիջի մասին, որը նշված է որպես փաստարկ, ներառյալ ընթացիկ ֆայլի ուղին.

Եթե ​​ենթադրենք, որ աղբյուրի տվյալների ֆայլը միշտ գտնվում է նույն պանակում, ինչ մեր Պրոցեսորը, ապա մեզ անհրաժեշտ ուղին կարող է ձևավորվել հետևյալ բանաձևով.

=LEFT(CELL(«ֆայլի անուն»);FIND(«[«;CELL(«ֆայլի անուն»))-1)&«Լավագույն 100 արտադրանք.xls"

կամ անգլերեն տարբերակով.

=LEFT(CELL(«ֆայլի անուն»);FIND(«[«;CELL(«ֆայլի անուն»))-1)&»Топ-100 товаров.xls»

… որտեղ է գործառույթը ԼԵՎՍԻՄՎ (ՁԱԽ) վերցնում է տեքստի մի կտոր ամբողջական հղումից մինչև բացվող քառակուսի փակագիծը (այսինքն՝ դեպի ընթացիկ թղթապանակ տանող ուղին), այնուհետև դրա վրա կպչում են մեր աղբյուրի տվյալների ֆայլի անունը և ընդլայնումը:

Պարամետրացնել ուղին հարցման մեջ

Մնում է վերջին և ամենակարևոր հպումը` հարցումում գրել աղբյուրի ֆայլի ուղին Լավագույն 100 ապրանքներ.xls, նկատի ունենալով մեր ստեղծված «խելացի» աղյուսակի A2 բջիջը Պարամետրեր.

Դա անելու համար եկեք վերադառնանք Power Query հարցումին և նորից բացենք այն Ընդլայնված խմբագիր ականջակալ տեսություն (Դիտել — Ընդլայնված խմբագիր). Տեքստի տող-ուղու փոխարեն չակերտներում «E: Sales ReportsTop 100 products.xlsx» Ներկայացնենք հետևյալ կառուցվածքը.

Excel.Current Workbook(){[Name=”Settings”]}[Բովանդակություն]0 {}[Ուղին դեպի աղբյուրի տվյալները]

Տեսնենք, թե ինչից է այն բաղկացած.

• Excel.Current Workbook() M լեզվի ֆունկցիան է՝ ընթացիկ ֆայլի բովանդակությանը մուտք գործելու համար
• {[Name=”Settings”]}[Բովանդակություն] – սա նախորդ ֆունկցիայի ճշգրտման պարամետր է, որը ցույց է տալիս, որ մենք ցանկանում ենք ստանալ «խելացի» աղյուսակի բովանդակությունը Պարամետրեր
• [Ուղին դեպի աղբյուրի տվյալները] աղյուսակի սյունակի անվանումն է Պարամետրերորին մենք անդրադառնում ենք
  
• 0 {} աղյուսակի տողի համարն է Պարամետրերորից մենք ցանկանում ենք տվյալներ վերցնել։ Գլխարկը չի հաշվում, և համարակալումը սկսվում է զրոյից, ոչ թե մեկից:

Այսքանը, փաստորեն:

Մնում է սեղմել Ավարտել և ստուգեք, թե ինչպես է աշխատում մեր հարցումը: Այժմ, երբ ամբողջ թղթապանակը երկու ֆայլով էլ ուղարկվում է մեկ այլ համակարգչի, հարցումը կմնա գործառնական և ինքնաբերաբար կորոշի տվյալների ուղին:

• Ինչ է Power Query-ն և ինչու է այն անհրաժեշտ Microsoft Excel-ում աշխատելիս
• Ինչպես ներմուծել լողացող տեքստի հատված Power Query-ում
• XNUMXD Crosstab-ի վերանախագծում հարթ սեղանի վրա Power Query-ով