Układy kalendarza do formatu tabelarycznego za pomocą dodatku Power Query

Z tego samouczka dowiesz się, jak przekształcać układy wielu typów kalendarzy w format tabelaryczny przy użyciu dodatku Power Query w usłudze LuckyTemplates. Dowiesz się, jak przekształcić wiele plików zamiast jednego pliku z folderu. Możesz obejrzeć pełny film tego samouczka na dole tego bloga.

Z tego samouczka dowiesz się również, jak uzupełniać luki w danych i jak zbierać je w usłudze LuckyTemplates.

Spis treści

• Przygotowanie plików i układów w dodatku Power Query
• Formułowanie obliczeń w dodatku Power Query
• Wypełnianie luk w datach
• Grupowanie oddzielnych tabel
• Wniosek

Przygotowanie plików i układów w dodatku Power Query

Jest to typowy układ plików i tygodni ułożonych poziomo i pionowo. W zależności od pliku liczba wierszy może się różnić ze względu na różnice w liczbie wierszy.

Teraz przejdź do dodatku Power Query i pobierz pliki. Wybierz Nowe źródło i kliknij Więcej. Następnie kliknij Wszystkie i wybierz Folder.

Wybierz Parametr i naciśnij OK.

Pokaże ci pliki w folderze. Musisz zebrać wszystkie pliki w jednym zapytaniu wynikowym. Kliknięcie przycisku Połącz i przekształć powoduje załadowanie danych i utworzenie szeregu zapytań.

Następnie wybierz dowolny plik w folderze jako plik przykładowy i wybierz dowolny element, który chcesz uruchomić.

Patrząc na Query1, możesz zobaczyć dane zarówno z File1, jak i File2.

Formułowanie obliczeń w dodatku Power Query

Format danych w Query1 jest nadal niepoprawny. Możesz to zmienić za pomocą zapytania Transform Sample File.

Jeśli zmienisz format tego zapytania na formę tabelaryczną, będzie ono służyć jako plan dla wszystkich plików w folderze.

Usuń pierwsze 2 wiersze i kolumnę 1.

Następnie uzupełnij luki wewnątrz tabeli. Przejdź do zakładki Przekształć i wybierz Transponuj. Wybierz kolumny zawierające daty i kliknij nagłówek. Kliknij Wypełnij i wybierz W dół.

Kolumny z datami na pasku formuły oznaczają, że jeśli w każdym tygodniu występuje różna liczba wierszy, wyniki nie będą w tych kolumnach. Usuń więc krok Wypełnione w sekcji Zastosowane kroki.

Każda grupa tabel zaczyna się od kolumny zawierającej daty. Musisz wykonać podstawowe operacje, aby przywrócić je do formatu tabelarycznego.

Dodaj krok ręczny, naciskając f(x) na pasku formuły. Następnie wprowadź Table.ToColumns . Spowoduje to przekształcenie każdej kolumny w listę zawierającą listy.

Przekształć listę w tabelę i dodaj numer indeksu. Możesz zmienić od 0 do 1.

Wypełnianie luk w datach

Nadal są luki w terminach, które należy uzupełnić. Jednak operacja wypełniania nie działa w przypadku list. Musisz przekształcić tę listę w tabelę.

Wybierz kolumnę Indeks i kliknij opcję Grupuj według. Zamień Count na ToTable, aby zmienić nazwę kolumny. Zmień RowCount na FromColumns i wskaż kolumnę 1, w której znajdują się listy. Następnie przekształć go w tabelę typów.

Możesz teraz zobaczyć, że lista została przekształcona w tabelę zawierającą tylko jedną kolumnę.

Teraz wykonaj operację wypełnienia dla każdej tabeli. Najpierw dodaj niestandardową kolumnę i nazwij ją FillDown .

Musisz uzyskać dostęp do pierwszej wartości pierwszej kolumny w tabeli, więc dla formuły użyj IF . Odwołaj się do kolumny ToTable, która zawiera tabelę. Wprowadź kolumnę 1 jako kolumnę, do której chcesz się odwołać. Następnie użyj null między nimi, aby pobrać pierwszą wartość z listy.

Następnie sprawdź, czy to randka. Jeśli tak, wypełnij tę kolumnę za pomocą Table.Filldown. Wewnątrz tej funkcji wprowadź ToTable i Column1. Ale jeśli nie jest to data, wprowadź ToTable, aby zwrócić całą tabelę.

To da ci nową kolumnę z wypełnionymi tabelami.

Znajdź miejsce umieszczenia dat w kolumnach i podziel je na osobne tabele. Aby to zrobić, musisz przekształcić tabele w listę kolumn.

Po funkcjach Table.FillDown i Else na pasku formuły odwołaj się do Kolumny1.

Spowoduje to przekształcenie tabel w listy z zestawem dat.

Następnie zmień listę z powrotem w tabelę, aktualizując kolumnę indeksu. Pamiętaj, że miejsce podziału to miejsce, w którym znajduje się kolumna daty. Dodaj więc niestandardową kolumnę i nazwij ją Index2 . Następnie sprawdź, czy lista zawiera daty.

Użyj IF i wprowadź FillDown. Wystarczy zaznaczyć pierwszą wartość na liście. Jeśli jest to data, zwróć Index. Jeśli nie, wprowadź null . Następnie wypełnij tę kolumnę.

Grupowanie oddzielnych tabel

Użyj opcji Grupuj według w kolumnie Indeks2. Spowoduje to zgrupowanie tabel z tymi samymi wartościami Index2 w jedną.

Nie będziesz liczyć rzędów. Zmień więc RowCount na FromColumns i dodaj kolumnę FillDown. Następnie zmień typ na table.

Jeśli klikniesz tabelę, zobaczysz znajdujące się w niej kolumny.

Następnie musisz dołączyć wszystkie oddzielne tabele do jednej tabeli. Utwórz ręczny krok i użyj Table.Combine. Następnie odwołaj się do znajdującej się w nim kolumny Count.

Wybierz Kolumna1 i Kolumna2. Kliknij Przekształć i wybierz Odwróć inne kolumny. Następnie pokaże ci 4 kolumny.

Usuń kolumnę Atrybut, ponieważ nie jest potrzebna. Następnie zmień nazwy kolumn na Date , Movements i Name . Zmień typ danych kolumny Data na Data i kolumny Nazwa na Tekst.

Zaktualizowałeś teraz przekształcenia do przykładowego pliku transformacji.

Jeśli przejdziesz do Zapytania 1, zobaczysz, że Kolumny 1 tabeli nie można znaleźć. Usuń więc krok Zmieniony typ w sekcji Zastosowane kroki.

Na koniec zmień typy danych w kolumnach. Ustaw typ danych kolumny Data na Data. Następnie ustaw typ danych w kolumnach Ruchy i Nazwa na Tekst.

Zaktualizowano i zmieniono format modelu danych na formę tabelaryczną.

Kalendarze niestandardowe usługi LuckyTemplates: obliczanie dla miesiąca po zmianie miesiąca — 445 kalendarzy
Techniki niestandardowego formatowania warunkowego w usłudze LuckyTemplates
Jak pracować z wieloma datami w usłudze LuckyTemplates

Wniosek

W raporcie dotyczącym danych musisz wypełnić luki w tabeli dat, przekształcając układy kalendarza w formę tabelaryczną.

Możesz przekształcić wiele kolumn za pomocą dodatku Power Query w usłudze LuckyTemplates. Dzięki temu Twój raport będzie zawierał prawidłowe i cenne informacje dotyczące zbiorów.

Wszystkiego najlepszego,

Melisa