Spis treści
Jeśli już zacząłeś korzystać z narzędzi darmowego dodatku Power Query w programie Microsoft Excel, to już niedługo napotkasz jeden wysoce wyspecjalizowany, ale bardzo częsty i denerwujący problem związany z ciągłym łamaniem linków do danych źródłowych. Istotą problemu jest to, że jeśli w zapytaniu odwołujesz się do zewnętrznych plików lub folderów, dodatek Power Query na stałe umieszcza do nich ścieżkę bezwzględną w tekście zapytania. Na twoim komputerze wszystko działa dobrze, ale jeśli zdecydujesz się wysłać plik z prośbą do swoich kolegów, to będą zawiedzeni, bo. mają inną ścieżkę do danych źródłowych na swoim komputerze, a nasze zapytanie nie zadziała.
Co zrobić w takiej sytuacji? Przyjrzyjmy się tej sprawie bardziej szczegółowo w poniższym przykładzie.
Sformułowanie problemu
Załóżmy, że mamy w folderze E:Raporty sprzedaży leży plik 100 najlepszych produktów.xls, który jest wgrywaniem z naszej korporacyjnej bazy danych lub systemu ERP (1C, SAP itp.) Ten plik zawiera informacje o najpopularniejszych pozycjach towarowych i wygląda tak w środku:
Prawdopodobnie od samego początku jest jasne, że praca z nim w Excelu w tej formie jest prawie niemożliwa: puste wiersze do jednego z danymi, scalone komórki, dodatkowe kolumny, wielopoziomowy nagłówek itp. będą przeszkadzać.
Dlatego obok tego pliku w tym samym folderze tworzymy kolejny nowy plik Handler.xlsx, w którym utworzymy zapytanie Power Query, które załaduje brzydkie dane ze źródłowego pliku przesyłania 100 najlepszych produktów.xls, i ułóż je w kolejności:
Wysyłanie żądania do pliku zewnętrznego
Otwieranie pliku Handler.xlsx, wybierz na karcie Dane Command Pobierz dane – z pliku – ze skoroszytu programu Excel (Dane — Pobierz dane — Z pliku — Z programu Excel), a następnie określ lokalizację pliku źródłowego i potrzebnego arkusza. Wybrane dane zostaną załadowane do edytora Power Query:
Przywróćmy je do normy:
- Usuń puste linie za pomocą Strona główna — Usuń linie — Usuń puste linie (Strona główna — Usuń wiersze — Usuń puste wiersze).
- Usuń niepotrzebne górne 4 wiersze przez Strona główna — Usuń wiersze — Usuń górne wiersze (Strona główna — Usuń wiersze — Usuń górne wiersze).
- Podnieś pierwszy wiersz do nagłówka tabeli za pomocą przycisku Użyj pierwszego wiersza jako nagłówków Zakładka Strona główna (Strona główna — Użyj pierwszego wiersza jako nagłówka).
- Oddziel pięciocyfrowy artykuł od nazwy produktu w drugiej kolumnie za pomocą polecenia podzielona kolumna Zakładka Transformacja (Przekształć — Podziel kolumnę).
- Usuń niepotrzebne kolumny i zmień nazwy nagłówków pozostałych, aby uzyskać lepszą widoczność.
W efekcie powinniśmy otrzymać następujący, znacznie przyjemniejszy obraz:
Pozostaje przesłać tę uszlachetnioną tabelę z powrotem do arkusza w naszym pliku Handler.xlsx drużyna zamknij i pobierz (Strona główna — Zamknij i załaduj) Zakładka Strona główna:
Znajdowanie ścieżki do pliku w żądaniu
Zobaczmy teraz, jak nasze zapytanie wygląda „pod maską”, w języku wewnętrznym wbudowanym w Power Query o zwięzłej nazwie „M”. Aby to zrobić, wróć do naszego zapytania, klikając je dwukrotnie w prawym okienku Żądania i połączenia i na karcie Review wybierać Zaawansowany edytor (Widok — Edytor zaawansowany):
W oknie, które się otworzy, druga linia natychmiast pokazuje zakodowaną ścieżkę do naszego oryginalnego pliku do przesłania. Jeśli możemy zastąpić ten ciąg tekstowy parametrem, zmienną lub łączem do komórki arkusza Excel, w której ta ścieżka jest wstępnie napisana, możemy ją później łatwo zmienić.
Dodaj inteligentną tabelę ze ścieżką do pliku
Zamknijmy na razie Power Query i wróćmy do naszego pliku Handler.xlsx. Dodajmy nowy pusty arkusz i zróbmy na nim małą „inteligentną” tabelkę, w jedynej komórce, w której zostanie zapisana pełna ścieżka do naszego źródłowego pliku danych:
Aby utworzyć inteligentną tabelę ze zwykłego zakresu, możesz użyć skrótu klawiaturowego Ctrl+T lub przycisk Formatuj jako tabelę Zakładka Strona główna (Strona główna — Formatuj jako tabelę). Nagłówek kolumny (komórka A1) może być absolutnie dowolny. Zauważ też, że dla jasności nadałem tabeli nazwę parametry Zakładka Konstruktor (Projekt).
Kopiowanie ścieżki z Eksploratora lub nawet ręczne wprowadzanie jej nie jest oczywiście szczególnie trudne, ale najlepiej zminimalizować czynnik ludzki i określić ścieżkę, jeśli to możliwe, automatycznie. Można to zaimplementować za pomocą standardowej funkcji arkusza Excel KOMÓRKA (KOMÓRKA), który może podać kilka przydatnych informacji o komórce określonej jako argument – w tym ścieżkę do bieżącego pliku:
Jeśli założymy, że źródłowy plik danych zawsze znajduje się w tym samym folderze co nasz Procesor, to potrzebna nam ścieżka może być utworzona według następującego wzoru:
=LEWA(KOMÓRKA("nazwa pliku");ZNAJDŹ("[";KOMÓRKA("nazwapliku"))-1)&"Top 100 produktów.xls"
lub w wersji angielskiej:
=LEWA(KOMÓRKA(«nazwa pliku»);ZNAJDŹ(«[«;KOMÓRKA(«nazwapliku»))-1)&»Топ-100 товаров.xls»
…gdzie jest funkcja LEVSIMV (LEWO) pobiera fragment tekstu od pełnego linku do otwierającego nawiasu kwadratowego (czyli ścieżki do bieżącego folderu), a następnie przykleja się do niego nazwę i rozszerzenie naszego źródłowego pliku danych.
Sparametryzuj ścieżkę w zapytaniu
Pozostaje ostatni i najważniejszy akcent – wpisać w żądaniu ścieżkę do pliku źródłowego 100 najlepszych produktów.xls, odnosząc się do komórki A2 stworzonej przez nas „inteligentnej” tabeli parametry.
Aby to zrobić, wróćmy do zapytania Power Query i otwórz je ponownie Zaawansowany edytor Zakładka Review (Widok — Edytor zaawansowany). Zamiast ciągu tekstowego w cudzysłowie „Raporty E:Sprzedaż 100 najlepszych produktów.xlsx” Przedstawmy następującą strukturę:
Excel.Bieżący skoroszyt(){[Nazwa=”Ustawienia”]}[Treść]0 {}[Ścieżka do danych źródłowych]
Zobaczmy, z czego składa się:
- Excel.Bieżący skoroszyt() jest funkcją języka M umożliwiającą dostęp do zawartości bieżącego pliku
- {[Nazwa=”Ustawienia”]}[Treść] – jest to parametr doprecyzowania poprzedniej funkcji, wskazujący, że chcemy uzyskać zawartość „inteligentnej” tabeli parametry
- [Ścieżka do danych źródłowych] to nazwa kolumny w tabeli parametrydo którego się odnosimy
- 0 {} to numer wiersza w tabeli parametryz którego chcemy pobrać dane. Czapka się nie liczy, a numeracja zaczyna się od zera, a nie od jedynki.
W rzeczywistości to wszystko.
Pozostaje kliknąć koniec i sprawdź jak działa nasza prośba. Teraz, gdy wysyłasz cały folder z obydwoma plikami wewnątrz do innego komputera, żądanie będzie nadal działać i automatycznie określi ścieżkę do danych.
- Co to jest dodatek Power Query i dlaczego jest potrzebny podczas pracy w programie Microsoft Excel
- Jak zaimportować pływający fragment tekstu do dodatku Power Query
- Przeprojektowanie tabeli krzyżowej XNUMXD na tabelę płaską za pomocą dodatku Power Query