Excel DAX - praca z tekstem i datami

DAX można używać w scenariuszach obejmujących pracę z tekstem, wyodrębnianie i komponowanie wartości daty i godziny lub tworzenie wartości na podstawie warunku. Za pomocą języka DAX możesz wykonać następujące czynności -

• Utwórz kolumnę kluczową w tabeli przez konkatenację.
• Utwórz datę w oparciu o części daty wyodrębnione z daty tekstowej.
• Zdefiniuj niestandardowy format daty.
• Zmień typy danych za pomocą formuły.
  • Zamień liczby rzeczywiste na liczby całkowite.
  • Konwertuj liczby rzeczywiste, liczby całkowite lub daty na ciągi.
  • Konwertuj ciągi na liczby rzeczywiste lub daty.

Tworzenie kolumny kluczowej przez konkatenację

Model danych w PowerPivot umożliwia tylko jedną kolumnę klucza. Nie obsługuje kluczy złożonych, które można znaleźć w zewnętrznych źródłach danych. Dlatego jeśli w tabeli w źródle danych istnieją jakiekolwiek klucze złożone, należy je połączyć w jedną kolumnę klucza dla tabeli w modelu danych.

Możesz użyć funkcji języka DAX CONCATENATE, aby połączyć dwie kolumny w jedną kolumnę w tabeli w modelu danych. Funkcja języka DAX CONCATENATE łączy dwa ciągi tekstowe w jeden ciąg tekstowy. Połączone elementy mogą być tekstem, liczbami lub wartościami logicznymi reprezentowanymi jako tekst lub kombinacją tych elementów. Możesz również użyć odwołania do kolumny, jeśli kolumna zawiera odpowiednie wartości.

= CONCATENATE ([Column1], [Column2])

Funkcja DAX CONCATENATE akceptuje tylko dwa argumenty. Jeśli którykolwiek z argumentów nie jest typu danych tekstowych, zostanie przekonwertowany na tekst. Funkcja DAX CONCATENATE zwraca połączony ciąg.

Data oparta na częściach daty wyodrębnionych z daty tekstowej

Model danych w dodatku Power Pivot obsługuje datę i godzinę typu danych dla wartości daty i godziny. Funkcje języka DAX, które działają na wartościach daty i / lub godziny, wymagają dla argumentów typu danych data-godzina.

Jeśli źródło danych zawiera daty w innym formacie, musisz najpierw wyodrębnić części daty za pomocą formuły języka DAX i połączyć te części, aby utworzyć prawidłowy typ danych DAX typu data / godzina.

Do wyodrębniania i tworzenia dat można używać następujących funkcji języka DAX -

DATE - Zwraca określoną datę w formacie daty i godziny.

DATEVALUE - Konwertuje datę w postaci tekstu na datę w formacie datetime.

TIMEVALUE - Konwertuje czas w formacie tekstowym na czas w formacie daty i godziny.

Definiowanie niestandardowego formatu daty

Załóżmy, że daty w źródle danych nie są reprezentowane w standardowym formacie. Można zdefiniować niestandardowy format daty, aby zapewnić poprawną obsługę wartości. Funkcja DAX FORMAT umożliwia konwersję wartości na tekst zgodnie z określonym formatem.

FORMAT (<value>, <format_string>)

Funkcja FORMAT zwraca ciąg znaków zawierający wartość sformatowaną zgodnie z definicją podaną przez format_string.

Możesz użyć wstępnie zdefiniowanych formatów daty i godziny lub możesz utworzyć formaty daty i godziny zdefiniowane przez użytkownika dla argumentu format_string funkcji FORMAT.

Poniżej przedstawiono wstępnie zdefiniowane nazwy formatów daty i czasu. Jeśli używasz ciągów innych niż te wstępnie zdefiniowane ciągi, zostaną one zinterpretowane jako niestandardowy format daty i godziny.

S. Nie.	Format_String i opis
1	"General Date" Wyświetla datę i / lub godzinę. Na przykład 10.02.2015 10:10:32
2	"Long Date" or "Medium Date" Wyświetla datę zgodnie z długim formatem daty. Na przykład środa, 7 marca 2016 r
3	"Short Date" Wyświetla datę w formacie daty krótkiej. Na przykład 2/03/2016
4	"Long Time" Wyświetla godzinę w formacie długiego czasu. Zwykle obejmuje godziny, minuty i sekundy. Na przykład 10:10:32
5	"Medium Time" Wyświetla czas w formacie 12-godzinnym. Na przykład 21:30
6	"Short Time" Wyświetla czas w formacie 24-godzinnym. Na przykład 14:15

Alternatywnie możesz użyć znaków z poniższej tabeli, aby utworzyć formaty daty / godziny zdefiniowane przez użytkownika.

S. Nie.	Opis postaci
1	: Separator czasu. Separator czasu. Oddziela godziny, minuty i sekundy podczas formatowania wartości czasu.
2	/ Separator daty. Oddziela dzień, miesiąc i rok podczas formatowania wartości dat.
3	% Służy do wskazania, że ​​następujący znak powinien być odczytywany jako format jednoliterowy, bez względu na jakiekolwiek litery końcowe. Służy również do wskazania, że ​​format jednoliterowy jest odczytywany jako format zdefiniowany przez użytkownika.

Poniżej znajdują się szczegóły dotyczące różnych postaci.

• %d - Wyświetla dzień jako liczbę bez wiodącego zera (np. 5).

• %dd - Wyświetla dzień jako liczbę z zerem wiodącym (np. 05).

• %ddd - Wyświetla dzień jako skrót (np. Słońce).

• %dddd - Wyświetla dzień jako pełną nazwę (np. Niedziela).

• %M - Wyświetla miesiąc jako liczbę bez wiodącego zera (np. Styczeń jest reprezentowany jako 1).

• %MM - Wyświetla miesiąc jako liczbę z początkowym zerem (np. Styczeń jest reprezentowany jako 01).

• %MMM - Wyświetla miesiąc jako skrót (np. Styczeń jest reprezentowany jako styczeń).

• %MMMM - Wyświetla miesiąc jako pełną nazwę miesiąca (np. Styczeń).

• %gg - Wyświetla ciąg okresu / ery (np. AD).

• %h- Wyświetla godzinę jako liczbę bez wiodących zer w formacie 12-godzinnym (np. 1:15:15). Posługiwać się%h jeśli jest to jedyny znak w formacie liczbowym zdefiniowanym przez użytkownika.

• %hh - Wyświetla godzinę jako liczbę z wiodącymi zerami w formacie 12-godzinnym (np. 13:15:15).

• %H- Wyświetla godzinę jako liczbę bez wiodących zer w formacie 24-godzinnym (np. 13:15:15, 1:15:15). Użyj% H, jeśli jest to jedyny znak w formacie liczbowym zdefiniowanym przez użytkownika.

• %HH - Wyświetla godzinę jako liczbę z wiodącymi zerami w formacie 24-godzinnym (np. 13:15:15, 1:15:15).

• %m- Wyświetla minuty jako liczbę bez wiodących zer (np. 2: 1: 15). Użyj% m, jeśli jest to jedyny znak w formacie liczbowym zdefiniowanym przez użytkownika.

• %mm - Wyświetla minuty jako liczbę z zerami na początku (np. 2:01:15).

• %s- Wyświetla sekundę jako liczbę bez wiodących zer (np. 2: 15: 5). Użyj% s, jeśli jest to jedyny znak w formacie liczbowym zdefiniowanym przez użytkownika.

• %ss - Wyświetla sekundę jako liczbę z zerami na początku (np. 2:15:05).

• %f- Wyświetla ułamki sekund. Npff wyświetla setne sekundy, podczas gdy ffffwyświetla dziesięć tysięcznych sekund. Możesz użyć maksymalnie siedmiufsymbole w formacie zdefiniowanym przez użytkownika. Posługiwać się%f jeśli jest to jedyny znak w formacie liczbowym zdefiniowanym przez użytkownika.

• %t- Używa zegara 12-godzinnego i wyświetla wielką literę A na dowolną godzinę przed południem; wyświetla wielką literę P dla dowolnej godziny między południem a 23:59 Użyj% t, jeśli jest to jedyny znak w formacie liczbowym zdefiniowanym przez użytkownika.

• %tt- W przypadku ustawień regionalnych, które używają zegara 12-godzinnego, wyświetla wielką literę AM z dowolną godziną przed południem; wyświetla wielką literę PM z dowolną godziną między południem a 23:59. W przypadku ustawień regionalnych, które używają zegara 24-godzinnego, nic nie wyświetla.

• %y- Wyświetla numer roku (0-9) bez wiodących zer. Posługiwać się%y jeśli jest to jedyny znak w formacie liczbowym zdefiniowanym przez użytkownika.

• %yy - Wyświetla rok w dwucyfrowym formacie numerycznym z początkowym zerem, jeśli ma to zastosowanie.

• %yyy - Wyświetla rok w czterocyfrowym formacie numerycznym.

• %yyyy - Wyświetla rok w czterocyfrowym formacie numerycznym.

• %z- Wyświetla przesunięcie strefy czasowej bez zera wiodącego (np. -8). Posługiwać się%z jeśli jest to jedyny znak w formacie liczbowym zdefiniowanym przez użytkownika.

• %zz- Wyświetla. przesunięcie strefy czasowej z zerem wiodącym (np. -08)

• %zzz - Wyświetla pełne przesunięcie strefy czasowej (np. -08: 00).

Jak widać, w łańcuchach formatowania rozróżniana jest wielkość liter. Można uzyskać inne formatowanie, używając innego przypadku.

Zmiana typów danych wyników formuł języka DAX

W formułach języka DAX typ danych wyjściowych jest określany przez kolumny źródłowe i nie można jawnie określić typu danych wyniku. Dzieje się tak, ponieważ optymalny typ danych jest określany przez dodatek Power Pivot. Można jednak użyć niejawnych konwersji typów danych wykonywanych przez dodatek Power Pivot do manipulowania typem danych wyjściowych. W przeciwnym razie możesz użyć pewnych funkcji języka DAX, aby przekonwertować typ danych wyjściowych.

Using the Implicit Data Type Conversions

• Aby przekonwertować datę lub ciąg liczbowy na liczbę, pomnóż przez 1,0. Na przykład = (DZISIAJ () + 5) * 1.0. Ta formuła oblicza bieżącą datę plus 5 dni i konwertuje wynik na wartość całkowitą.

  • Aby przekonwertować datę, liczbę lub wartość waluty na ciąg, połącz wartość z pustym ciągiem. Na przykład = Dzisiaj () & „”

Using the DAX Functions for Data Type Conversions

Funkcji języka DAX można używać do następujących celów -

• Zamiana liczb rzeczywistych na liczby całkowite.
• Zamiana liczb rzeczywistych, liczb całkowitych lub dat na ciągi.
• Konwersja ciągów znaków na liczby rzeczywiste lub daty.

Dowiesz się tego w kolejnych sekcjach.

Zamiana liczb rzeczywistych na liczby całkowite

Możesz użyć następujących funkcji języka DAX do konwersji liczb rzeczywistych na liczby całkowite -

ROUND (<number>, <num_digits>) - Zaokrągla liczbę do określonej liczby cyfr i zwraca liczbę dziesiętną.

CEILING (<number>, <significance>) - zaokrągla liczbę w górę do najbliższej liczby całkowitej lub do najbliższej wielokrotności istotności i zwraca liczbę dziesiętną.

FLOOR (<number>, <significance>) - Zaokrągla liczbę w dół, w kierunku zera, do najbliższej wielokrotności istotności i zwraca liczbę dziesiętną.

Konwersja liczb rzeczywistych, liczb całkowitych lub dat na ciągi

Możesz użyć następujących funkcji języka DAX do konwertowania liczb rzeczywistych, liczb całkowitych lub dat na ciągi -

FIXED (<number>, [<decimals>], [<no_comma>])- Zaokrągla liczbę i zwraca wynik jako tekst. Liczba cyfr po prawej stronie przecinka dziesiętnego to 2 lub określona liczba miejsc po przecinku. Wynik jest podawany przecinkami lub opcjonalnie bez przecinków.

FORMAT (<value>, <format_string>) - Konwertuje wartość na tekst zgodnie z określonym formatem.

Dowiedziałeś się już o używaniu funkcji Format do konwersji dat na ciągi.

Konwersja ciągów znaków na liczby rzeczywiste lub daty

Za pomocą następujących funkcji języka DAX można konwertować ciągi na liczby rzeczywiste lub daty -

VALUE (<text>) - Konwertuje ciąg tekstowy reprezentujący liczbę na liczbę.

DATEVALUE (date_text) - Konwertuje datę w postaci tekstu na datę w formacie datetime.

TIMEVALUE (time_text) - Konwertuje czas w formacie tekstowym na czas w formacie daty i godziny.