Dołączanie kilku arkuszy w programie Excel do usługi LuckyTemplates

W tym samouczku omówiono sposób importowania i otwierania pliku programu Excel z wieloma arkuszami do jednej tabeli usługi LuckyTemplates.

Ten plik programu Excel z danymi zostanie użyty w tym samouczku.

Plik zawiera 60 arkuszy danych o tym samym układzie.

Celem tego samouczka jest załadowanie wszystkich arkuszy programu Excel do usługi LuckyTemplates i pobranie informacji o obligacjach w postaci jednej tabeli. Musisz zdobyć pasek bezpieczeństwa i dodać go w osobnej kolumnie. Następnie dołącz je wszystkie razem w jednej tabeli, która wykorzystuje interfejs użytkownika do generowania potrzebnego kodu.

Spis treści

• Importowanie arkuszy Excela do LuckyTemplates
• Wydobywanie paska zabezpieczeń z programu Excel do usługi LuckyTemplates
• Usuwanie pustych wierszy
• Duplikowanie zapytania
• Przypisywanie typów danych do kolumn
• Dodawanie kolumny paska bezpieczeństwa z programu Excel do usługi LuckyTemplates
• Tworzenie niestandardowej kolumny dla zapytania Bond
• Wniosek

Importowanie arkuszy Excela do LuckyTemplates

Pierwszą rzeczą do zrobienia jest otwarcie edytora Power Query i utworzenie parametru do przechowywania lokalizacji pliku za pomocą opcji Zarządzaj parametrami .

W polu Zarządzaj parametrami kliknij Nowy i wprowadź FileLocation jako nazwę parametru. Następnie ustaw Typ danych na Tekst , a Sugerowane wartości na Dowolna wartość . Skopiuj ścieżkę pliku programu Excel i wklej ją w polach Bieżąca wartość.

Po naciśnięciu OK kliknij Nowe źródło i wybierz Skoroszyt programu Excel .

Następnie wybierz i otwórz plik Excel. Po zakończeniu ekran Nawigatora otworzy się i wyświetli listę wszystkich arkuszy w pliku Excel.

Wybierz pierwszy arkusz i naciśnij OK. Następnie zobaczysz tabelę w edytorze dodatku Power Query.

Następną rzeczą do zrobienia jest zmiana zakodowanej ścieżki pliku na parametr pliku. Otwórz okno Edytora zaawansowanego i zmień ścieżkę pliku w kodzie źródłowym na FileLocation .

Najlepiej przechowywać dane w tabelach programu Excel, ponieważ granice lub zakres danych są definiowane przed wprowadzeniem ich do dodatku Power Query . Ponieważ plik zawiera arkusze i nie ma tabel, istnieje ryzyko wprowadzenia pustych kolumn i pustych wierszy, więc musisz to uporządkować.

Wydobywanie paska zabezpieczeń z programu Excel do usługi LuckyTemplates

Świadomość, że układ arkuszy jest stały, pomaga w budowaniu rozwiązania, zwłaszcza jeśli chcesz zmaksymalizować i użyć interfejsu użytkownika do wygenerowania kodu. Na przykład jednym wymaganiem jest dodanie kolumny zawierającej znacznik bezpieczeństwa. Jeśli spojrzysz na dane, zobaczysz ticker.

Interfejs użytkownika może pomóc w wyodrębnieniu wartości paska. Kliknij komórkę prawym przyciskiem myszy i wybierz opcję Dodaj jako nowe zapytanie .

Na pasku formuły zobaczysz, że po nazwie tabeli występuje numer wiersza liczony od zera w nawiasach klamrowych. Nawiasy klamrowe nazywane są operatorami indeksu pozycyjnego . Zobaczysz również nazwę pola ujętą w nawiasy kwadratowe, które są nazywane operatorami dostępu do pól .

Za pomocą tych kodów możesz teraz wyodrębnić wartość. Wróć do tabeli Bond i uzyskaj dane Bonda. Najpierw usuń osiem górnych ośmiu rzędów. Kliknij Usuń wiersze i wybierz Usuń górne wiersze .

Następnie wprowadź 8 w polu Liczba wierszy i naciśnij OK.

Następnie kliknij opcję Użyj pierwszego wiersza jako nagłówków, aby ustawić nagłówki.

Po zakończeniu tabela obligacji będzie teraz wyglądać tak.

Bezpośrednio pod nagłówkiem zobaczysz cienką linię reprezentującą jakość kolumny. Stamtąd widać, że w kolumnach jest sporo pustych miejsc. Oznacza to, że plik przyniósł wiele pustych wierszy.

Usuwanie pustych wierszy

Aby usunąć puste wiersze, kliknij Usuń wiersze i wybierz Usuń puste wiersze .

Ta transformacja generuje następującą składnię:

Funkcja Record.FieldValues ​​pobiera wszystkie wartości z bieżącego wiersza w tabeli jako listę. List.RemoveMatchingItems usuwa wszystkie wartości z pierwszej listy, które mają dopasowanie na drugiej liście. Druga lista zawiera tylko pusty ciąg tekstowy lub wartość null. Są to wartości, które zostaną wykluczone z pierwszej listy.

Jeśli wszystkie puste ciągi tekstowe i wartość null zostały usunięte z listy z wartościami pola rekordu, lista powinna być pusta, a List.IsEmpty zostanie oceniona jako prawda. Następnie Table.SelectRows zachowa wartości Trues.

Nie powinieneś skończyć z tabelą zawierającą tylko puste wiersze. Dlatego słowo kluczowe not jest dodawane przed List.IsEmpty . To następnie zwraca tabelę zawierającą niepuste wiersze.

Oprócz pustych wierszy musisz także usunąć puste kolumny. Ale zanim to zrobisz, spójrz, co Power Query generuje po usunięciu kolumny. Wybierz czwartą kolumnę i kliknij Usuń kolumny .

Po wykonaniu transformacji zobaczysz tę składnię na pasku formuły.

Wywołuje funkcję Table.RemoveColumns , a następnie odwołuje się do poprzedniego kroku i przekazuje go w okienku Zastosowane kroki jako pierwszy argument. Transformacja dalej przekazuje listę zawierającą nazwy kolumn, które chcesz usunąć.

Duplikowanie zapytania

Teraz zduplikuj zapytanie i wybierz krok Promuj nagłówki w okienku Zastosowane kroki. Następnie kliknij ten krok prawym przyciskiem myszy i wybierz Usuń do końca .

Pamiętaj, że możesz użyć operatora indeksu pozycyjnego, aby przekazać numer wiersza liczony od zera w zestawie nawiasów klamrowych. Dlatego zachowaj tylko pierwszy wiersz, wprowadzając 0 w dwóch nawiasach klamrowych na pasku formuły.

Po zakończeniu tabela Bonda będzie wyglądać tak.

Stamtąd ponownie użyj części logiki, która została utworzona w kroku Usunięto puste wiersze, aby przekształcić rekord w listę i usunąć wartość null. Wróć do zapytania Bond i wybierz krok Usuń puste wiersze. Następnie skopiuj ten kod M.

Wróć do zduplikowanego zapytania i wklej kod na pasku formuły. Następnie ułóż kilka ciągów, aby formuła wyglądała tak.

Utworzyłeś teraz listę z nazwami kolumn, które chcesz zachować. Zmień nazwę zapytania Nazwy kolumn .

Następnie wróć do zapytania Bonda. Ponieważ utworzono kwerendę zawierającą wszystkie kolumny, które chcesz zachować, w funkcji Table.RemoveColumns musisz wyświetlić listę wszystkich kolumn, które chcesz wykluczyć .

Zmień {Column4} z tą samą składnią, która została skopiowana z kroków Usunięto puste wiersze. Zmień także {“”, null} na ColumnNames .

Następnie musisz przekazać listę z rzeczywistymi nazwami kolumn z tabeli Bond. Zmień Record.FieldValues(_) na Table.ColumnNames() . Wpisz # „Usunięto puste wiersze” w nawiasach, aby przekazać argument odwołania do tabeli.

Przypisywanie typów danych do kolumn

Następną rzeczą do zrobienia jest przypisanie odpowiednich typów danych do kolumn. W kolumnie Data kliknij ikonę obok nagłówka i wybierz Data.

Dla kolumn PX_LAST i YLD_YTM_MID wybierz typ danych Liczba dziesiętna.

Za pomocą tych trzech zapytań utworzono wszystkie elementy składowe, których można użyć do zaprojektowania rozwiązania odnoszącego się do wszystkich arkuszy w pliku Excel . W tym celu należy zduplikować zapytanie Bonda i usunąć wszystkie kroki z wyjątkiem kroku Źródło w okienku Zastosowane kroki.

Dodawanie kolumny paska bezpieczeństwa z programu Excel do usługi LuckyTemplates

W kroku Źródło możesz zobaczyć wszystkie dane w pliku Excel. Zamiast tworzyć oddzielne zapytanie dla każdego arkusza, możesz użyć zapytania Bond i przekształcić zagnieżdżone tabele w kolumnie Dane.

Najpierw dodaj znacznik bezpieczeństwa. Jeśli klikniesz białe miejsce w komórce dowolnej tabeli, zobaczysz podgląd zawartości tej tabeli.

Musisz utworzyć logikę za pomocą instrukcji , aby uzyskać znacznik bezpieczeństwa. Przejdź do zapytania Column2 i skopiuj adres paska zabezpieczeń z paska formuły.

Następnie wróć do zapytania Bonda i dodaj niestandardową kolumnę.

Nazwij kolumnę Security Ticker i wpisz następujący kod M.

Formuła zawiera instrukcję JEŻELI mówiącą, że jeśli słowo Zabezpieczenia zostanie znalezione w Kolumnie 1, to poda wartość sąsiadującej z nią komórki z Kolumny 2. W przeciwnym razie zostanie podana wartość null.

Po naciśnięciu OK do tabeli zostanie dodana nowa kolumna ze znacznikami bezpieczeństwa.

Kliknij rozwijany przycisk kolumny Security Ticker i odznacz wartość null, aby usunąć wszystkie wartości null w kolumnie.

Następnie otrzymasz wszystkie informacje o Obligacjach ze wszystkich arkuszy. Wystarczy powtórzyć wykonane transformacje w zapytaniu Bond(2) i zastosować je do zagnieżdżonych tabel w kolumnie Dane.

Tworzenie niestandardowej kolumny dla zapytania Bond

Przejdź do zapytania Bond(2), otwórz okno Edytora zaawansowanego i skopiuj następujący kod:

Następnie wróć do zapytania Bonda i utwórz kolejną niestandardową kolumnę. Ponieważ musisz zastosować wiele przekształceń w wielu krokach, musisz użyć instrukcji let . Więc wprowadź let i wklej kod z Edytora zaawansowanego.

Następnie zmień Bond_Sheet na Dane , aby przekształcić tabelę w kolumnie Dane.

Po naciśnięciu OK w tabeli zostanie dodana nowa kolumna.

Jeśli klikniesz spację w dowolnej komórce tej kolumny, zobaczysz dane z zapytania Bond(2).

Wszystko, czego potrzebujesz, to dwie ostatnie kolumny zapytania Bonda. Wybierz więc kolumny Security Ticker i Custom i kliknij Usuń inne kolumny na wstążce Usuń kolumny.

Rozwiń kolumnę Niestandardowe i odznacz pole Użyj oryginalnej nazwy jako prefiksu.

Po zakończeniu sprawdź poprawność tabeli. Pod tabelą wybierz opcję Profilowanie kolumn na podstawie całego zestawu danych .

Po tej transformacji w kolumnie Dane wystąpi błąd.

Sprawdź więc błąd, klikając kolumnę Dane i wybierając opcję Zachowaj błędy na wstążce Zachowaj wiersze.

Jeśli klikniesz wartość błędu w kolumnie Dane, możesz przeczytać poniższy komunikat o błędzie:

Aby rozwiązać problemy, najpierw usuń krok Zachowane błędy. Kliknij kolumnę Dane i wybierz Usuń błędy ze wstążki Usuń wiersze.

Następnie ustaw opcję profilowania kolumn z powrotem na 1000 pierwszych wierszy. I to wszystko!

Wniosek

To rozwiązanie oparte na interfejsie użytkownika może pomóc w dołączaniu wielu arkuszy z pliku programu Excel do usługi LuckyTemplates. Zamiast tworzyć 60 oddzielnych zapytań i wielokrotnie wykonywać wszystkie przekształcenia, to rozwiązanie może utworzyć pojedyncze zapytanie, które wykona wszystkie przekształcenia. Wykorzystaj i zmaksymalizuj to rozwiązanie, aby stworzyć doskonały raport z danymi .

Melisa