Tworzenie tabeli dat
Podczas tworzenia raportu w usłudze Power BI typowym wymaganiem biznesowym jest możliwość wykonywania obliczeń na podstawie dat i godzin. Organizacja potrzebuje informacji na temat tego, jak firma radzi sobie w poszczególnych miesiącach, kwartałach, latach podatkowych itd. Z tego powodu prawidłowe sformatowanie wartości czasowych jest niezbędne. W usłudze Power BI kolumny i tabele dat są wykrywane automatycznie, może się jednak zdarzyć, że sformatowanie dat zgodnie z potrzebami organizacji będzie wymagało wykonania pewnych dodatkowych czynności.
Załóżmy na przykład, że opracowujesz raport dla zespołu sprzedaży w swojej organizacji. Baza danych zawiera tabele z danymi dotyczącymi sprzedaży, zamówień, produktów i nie tylko. Można zauważyć, że wiele z tych tabel, na przykład Sales (Sprzedaż) i Orders (Zamówienia), zawiera własne kolumny daty — widzimy tu kolumny ShipDate (Data dostawy) i OrderDate (Data zamówienia) w tabelach Sales i Orders. Twoim zadaniem jest opracowanie tabeli przedstawiającej sumę sprzedaży i zamówień według lat i miesięcy. Jak można zbudować wizualizację na podstawie wielu tabel, z których każda odwołuje się do własnych kolumn dat?
Aby rozwiązać ten problem, możesz utworzyć wspólną tabelę dat, która może być używana przez wiele tabel. W poniższej sekcji wyjaśniono, jak wykonać to zadanie w usłudze Power BI.
Tworzenie wspólnej tabeli dat
Są następujące sposoby na utworzenie wspólnej tabeli dat:
Dane źródłowe
Język DAX
Dodatek Power Query
Dane źródłowe
Może się zdarzyć, że źródłowa baza danych czy magazyn danych zawiera już tabelę dat. Jeśli administrator dobrze zaprojektował bazę danych, te tabele mogą być używane do wykonywania takich zadań jak:
identyfikowanie dni wolnych w firmie,
rozróżnienie lat kalendarzowych i podatkowych,
identyfikowanie dni roboczych i weekendów.
Źródłowe tabele danych są kompletne i gotowe do natychmiastowego użycia. Jeśli masz tabelę jako taką, przeprowadź ją do modelu semantycznego i nie używaj żadnych innych metod opisanych w tej sekcji. Zalecamy korzystanie ze źródłowej tabeli danych, ponieważ prawdopodobnie jest ona współużytkowana także w innych narzędziach, których używasz, poza usługą Power BI.
Jeśli nie masz takiej źródłowej tabeli danych, możesz utworzyć wspólną tabelę dat innymi metodami.
Język DAX
Aby utworzyć wspólną tabelę dat, możesz użyć funkcji języka DAX (Data Analysis Expression) takich jak CALENDARAUTO() lub CALENDAR(). Funkcja CALENDAR() zwraca ciągły zakres dat na podstawie daty rozpoczęcia i daty zakończenia, wprowadzonych jako argumenty funkcji. Alternatywnie funkcja CALENDARAUTO() zwraca ciągły, pełny zakres dat, które są automatycznie określane z modelu semantycznego. Data początkowa jest wybierana jako najwcześniejsza data, która istnieje w modelu semantycznym, a data zakończenia to najnowsza data, która istnieje w modelu semantycznym oraz dane wypełnione miesiącem obrachunkowym, który można uwzględnić jako argument w funkcji CALENDARAUTO(). Na potrzeby tego przykładu jest używana funkcja CALENDAR(), ponieważ chcemy wyświetlić tylko dane od 31 maja 2011 r. (pierwszego dnia rejestrowania tych danych przez dział sprzedaży) z kolejnych 10 lat.
W Power BI Desktop wybierz pozycję Nowa tabela, a następnie wprowadź następującą formułę języka DAX:
Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
Masz teraz kolumnę dat, której możesz użyć. Ta kolumna jest jednak dość rozrzedzona. Potrzebujesz także kolumn przedstawiających tylko rok, numer miesiąca, tydzień roku oraz dzień tygodnia. To zadanie można wykonać, wybierając pozycję Nowa kolumna na wstążce i wprowadzając następujące wyrażenie języka DAX, umożliwiające pobranie roku z kolumny dat.
Year = YEAR(Dates[Date])
Za pomocą tej samej procedury można pobrać numer miesiąca, numer tygodnia oraz dzień tygodnia:
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
Po zakończeniu tabela będzie zawierała kolumny widoczne na poniższej ilustracji.
Masz teraz wspólną tabelę dat utworzoną przy użyciu języka DAX. Ten proces dodaje tylko nową tabelę do modelu semantycznego; Nadal musisz ustanowić relacje między tabelą dat a tabelami Sales and Order, a następnie oznaczyć tabelę jako oficjalną tabelę dat modelu semantycznego. Zanim jednak przejdziemy do tych zadań, należy rozważyć jeszcze inny sposób tworzenia wspólnej tabeli dat: przy użyciu dodatku Power Query.
Dodatek Power Query
Do zdefiniowania wspólnej tabeli dat możesz użyć języka M, czyli języka programowania używanego do tworzenia zapytań w dodatku Power Query.
Wybierz pozycję Przekształć dane w programie Power BI Desktop, co spowoduje przejście do dodatku Power Query. W pustym obszarze okienka Zapytania po lewej stronie kliknij prawym przyciskiem myszy, aby otworzyć następujące menu rozwijane, w którym wybierzesz pozycję Nowe zapytanie puste zapytanie>.
Po wyświetleniu widoku Nowe zapytanie, wprowadź następującą formułę języka M, aby utworzyć tabelę kalendarza:
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
W przypadku danych sprzedaży data rozpoczęcia ma odzwierciedlać najwcześniejszą datę zawartą w danych: 31 maja 2011 r. Ponadto chcesz uwzględnić kolejne 10 lat, włącznie z datami przypadającymi w przyszłości. Dzięki takiemu podejściu nie trzeba będzie ponownie tworzyć tej tabeli w miarę napływu nowych danych o sprzedaży. Możesz również zmienić czas trwania. W tym przypadku potrzebujesz jednego punktu danych na każdy dzień, ale można też dodać przyrosty godzinowe, minutowe, czy sekundowe. Na poniższej ilustracji przedstawiono rezultat.
Po pomyślnym zrealizowaniu tego procesu zobaczysz, że masz listę dat zamiast tabeli. Aby rozwiązać ten błąd, przejdź do karty Przekształć na wstążce i wybierz pozycję Konwertuj > na tabelę. Jak sama nazwa wskazuje, ta funkcja umożliwia przekonwertowanie listy na tabelę. Możesz też zmienić nazwę kolumny na DateCol (Kolumna daty).
Następnie należy dodać kolumny do nowej tabeli, aby wyświetlić daty według roku, miesiąca, tygodnia i dnia, co umożliwi utworzenie hierarchii w wizualizacji. Pierwszym zadaniem do wykonania jest zmiana typu kolumny przez wybranie ikony obok jej nazwy, a następnie wybranie typu Data w wyświetlonym menu rozwijanym.
Po wybraniu typu Data możesz dodać kolumny roku, miesiąca, tygodnia i dnia. Przejdź do pozycji Dodaj kolumnę wybierz menu rozwijane w obszarze Data, a następnie wybierz pozycję Rok, tak jak pokazano na poniższej ilustracji.
Zwróć uwagę, że w usłudze Power BI została dodana kolumna zawierająca wszystkie lata pobrane z kolumny DateCol.
Wykonaj tę samą procedurę, aby utworzyć kolumny miesięcy, tygodni i dni. Po zakończeniu tej procedury tabela będzie zawierała kolumny widoczne na poniższej ilustracji.
Udało Ci się utworzyć wspólną tabelę dat przy użyciu dodatku Power Query.
W poprzednich krokach pokazano, jak uzyskać tabelę do modelu semantycznego. Musisz teraz oznaczyć tę tabelę jako oficjalną tabelę dat, aby zapewnić, że formatowanie jest prawidłowe, a usługa Power BI będzie ją rozpoznawać na potrzeby wszystkich przyszłych wartości.
Oznaczanie tabeli jako oficjalnej tabeli dat
Pierwszym zadaniem w procesie oznaczania nowej tabeli jako oficjalnej tabeli dat jest znalezienie jej w okienku Pola. Kliknij prawym przyciskiem myszy nazwę tabeli, a następnie wybierz polecenie Oznacz jako tabelę dat, jak pokazano na poniższej ilustracji.
Gdy oznaczysz tabelę jako tabelę dat, w usłudze Power BI zostanie przeprowadzona walidacja, aby zapewnić, że tabela nie zawiera wartości null, zawiera ciągłe wartości dat w danym okresie, a zawarte w niej dane są unikatowe. Możesz też oznaczyć jako źródło dat wybrane kolumny w tabeli, co może się przydać, jeśli tabela zawiera wiele kolumn. Kliknij prawym przyciskiem myszy tabelę, wybierz pozycję Oznacz jako tabelę dat, a następnie wybierz pozycję Ustawienia tabeli dat. Zostanie wyświetlone następujące okno, w którym można wybrać kolumnę, która ma być oznaczona jako Data.
Wybranie polecenia Oznacz jako tabelę dat spowoduje usunięcie automatycznie wygenerowanych hierarchii z pola Data w tabeli oznaczonej jako tabela dat. W przypadku innych pól daty automatyczna hierarchia będzie nadal istnieć do momentu ustanowienia relacji między tym polem a tabelą dat lub wyłączenia funkcji Automatyczna data/godzina. Możesz ręcznie dodać hierarchię do wspólnej tabeli dat, klikając prawym przyciskiem myszy kolumny rok, miesiąc, tydzień lub dzień w okienku Pola , a następnie wybierając pozycję Nowa hierarchia. Ten proces jest dokładniej omówiony w dalszej części tego modułu.
Tworzenie wizualizacji
Aby utworzyć wizualizację danych z tabel Sales i Orders, należy dodać relację między nową wspólną tabelą dat a tabelami Sales i Orders. To umożliwi tworzenie wizualizacji przy użyciu nowej tabeli dat. Aby wykonać to zadanie, przejdź do pozycji Modelowanie>Zarządzanie relacjami, gdzie możesz utworzyć relacje między wspólną tabelą dat a tabelami Orders i Sales przy użyciu kolumny OrderDate. Poniższy zrzut ekranu przedstawia przykład jednej z tych relacji.
Po utworzeniu relacji możesz zbudować wizualizację łącznej sprzedaży i zamawianych ilości w czasie, używając wspólnej tabeli dat opracowanej przy użyciu języka DAX lub dodatku Power Query.
Aby określić łączną sprzedaż, musisz dodać wszystkie wartości sprzedaży, ponieważ kolumna Amount (Kwota) w kolumnie Sales przedstawia tylko przychód z poszczególnych transakcji sprzedaży, a nie łączny przychód ze sprzedaży. To zadanie można wykonać przy użyciu następującego obliczenia miary, które zostanie dokładniej wyjaśnione później. Do utworzenia miary użyjesz następującego obliczenia:
#Total Sales = SUM(Sales[‘Amount’])
Po zakończeniu możesz utworzyć tabelę, wracając do karty Wizualizacje i wybierając wizualizację Tabela. Chcesz zobaczyć sumę zamówień i sprzedaży w poszczególnych latach i miesiącach, a więc uwzględnisz tylko kolumny Year (Rok) i Month (Miesiąc) z tabeli dat, kolumnę OrderQty (Zamówiona ilość) i miarę #TotalSales (Łączna sprzedaż). Gdy dowiesz się więcej o hierarchiach, możesz też utworzyć hierarchię umożliwiającą przechodzenie do szczegółów — od lat do miesięcy. Na potrzeby tego przykładu można pokazać je obok siebie. Masz teraz utworzoną wizualizację z użyciem wspólnej tabeli dat.
