Wyrażenia zagnieżdżone: środowiska Power Query

W tym samouczku dowiesz się o zagnieżdżonych wyrażeniach, obiektach i środowiskach w dodatku Power Query . Zagnieżdżone wyrażenia i zmienne zostaną użyte do omówienia działania środowisk i pomocy w generowaniu danych. Dowiesz się również, jak radzić sobie z typowymi scenariuszami podczas przekształcania obiektów zagnieżdżonych.

Spis treści

• Tworzenie wyrażeń zagnieżdżonych
• Zrozumienie wyrażeń zagnieżdżonych w środowisku
• Rozwiązywanie typowych scenariuszy
  • Używanie zmiennych do przechowywania wartości
  • Pobieranie pojedynczej wartości
• Wniosek

Tworzenie wyrażeń zagnieżdżonych

Wyrażenia są definiowane jako formuły używane do konstruowania wartości.

Wyrażenie na pasku formuły jest zbudowane z wyrażeń podrzędnych. Literały 1 i 2 są wyrażeniami podrzędnymi wyrażenia nadrzędnego.

Zmienne są definiowane jako nazwane wartości. To jest przykładowy zapis.

Jeśli otworzysz okno Edytora zaawansowanego, zobaczysz zmienne użyte w zapytaniu.

Każda zmienna a , b i c reprezentuje wartość będącą wynikiem wyrażenia, które często można znaleźć po znaku równości. Wszystkie zmienne w wyrażeniu nadrzędnym, które jest samym rekordem, tworzą środowisko tego rekordu. W tym środowisku każda zmienna musi być unikalna.

Jeśli więc dodasz kolejną zmienną o nazwie c , zobaczysz komunikat o błędzie pod oknem Edytora zaawansowanego.

Rekord może uzyskiwać dostęp do wszystkich swoich wyrażeń podrzędnych za pomocą identyfikatorów lub nazw zmiennych. Jednak w przypadku wyrażeń zagnieżdżonych w rekordzie każde środowisko jest inne, ponieważ mogą one uzyskiwać dostęp do wszystkich innych zmiennych w rekordzie z wyjątkiem ich samych.

W tym przykładzie widać, że zmienna c odnosi się do samej siebie.

Jeśli naciśniesz gotowe, pojawi się komunikat o błędzie.

Jeśli wyodrębnisz wartość c przez dodanie operatora dostępu do elementu, otrzymasz wynik. Ten sam wynik można również uzyskać za pomocą wyrażenia let , ponieważ mają do niego zastosowanie te same reguły.

Zrozumienie wyrażeń zagnieżdżonych w środowisku

To kolejny przykład rekordowej wartości.

Jeśli otworzysz okno Edytora zaawansowanego, zobaczysz, że zmienne aib dzielą to samo środowisko, które jest najbardziej zewnętrznym środowiskiem . Zobaczysz również, że zmienne x , y i z są wyrażeniami podrzędnymi wyrażenia nadrzędnego a .

Każda zmienna może uzyskiwać dostęp do wszystkich innych zmiennych w zagnieżdżonym rekordzie z wyjątkiem siebie. Możesz wywołać zmienną, która istnieje w innym środowisku. W tym przykładzie widać, że z odnosi się do b , które znajduje się w środowisku zewnętrznym.

Zmienne x , y i z mogą uzyskiwać dostęp do b , ponieważ b jest częścią ich nadrzędnego środowiska wyrażeń. Możesz także mieć tę samą zmienną, ale w różnych środowiskach.

W tym innym przykładzie widać, że wewnętrzny i zewnętrzny rekord ma zmienną x .

Zmienne muszą być unikalne w swoim własnym środowisku. Zostanie użyty najbliższy x przypisany do zmiennej. W tym przykładowym zapytaniu używany jest wewnętrzny x , ponieważ jest on bliższy z punktu widzenia rekordu a .

W tym innym zapytaniu w wyrażeniu używane jest najbliższe x , które znajduje się w pobliżu zmiennej b .

Zmienna zewnętrzna b może odnosić się do zmiennej x wewnątrz rekordu a za pomocą operatora dostępu do elementu. Musisz odnieść się do zmiennej a , a następnie uzyskać dostęp do x za pomocą zestawu nawiasów kwadratowych. W ten sam sposób zapis a może odnosić się do zmiennej x wewnątrz zmiennej b .

Gdy środowisko zagnieżdżonego rekordu a jest łączone ze zmiennymi z wyrażenia nadrzędnego, prowadzi to do konfliktu, ponieważ x występuje dwukrotnie, a wszystkie zmienne muszą być unikalne w swoim środowisku. Ponieważ zmienna nie może odnosić się do siebie, konflikt rozwiązuje się, odwołując się do zewnętrznego x.

Rozwiązywanie typowych scenariuszy

Typowym scenariuszem jest przekształcanie obiektów zagnieżdżonych. Aby zademonstrować, zostanie użyta ta tabela.

Jeśli chcesz pobrać wartość z tabeli zewnętrznej i umieścić ją w tabeli zagnieżdżonej, możesz to osiągnąć na dwa sposoby. Pierwsza metoda polega na użyciu opcji Dodaj kolumnę niestandardową .

Kliknij ikonę tabeli w lewym górnym rogu okienka podglądu i wybierz opcję Dodaj kolumnę niestandardową.

Wprowadź Temp jako nazwę kolumny i wprowadź symbol zastępczy w formule kolumny niestandardowej . Po zakończeniu naciśnij OK.

Wewnątrz paska formuły zastąp każde wyrażenie funkcją niestandardową, dodając kolumnę do zagnieżdżonej tabeli. Uruchom logikę dla funkcji niestandardowej i nazwij ją OT dla tabeli zewnętrznej. Następnie dodaj znak przejścia.

Następnie użyj funkcji Table.AddColumn , aby dodać kolumnę. Zapoznaj się z tabelą w zewnętrznej tabeli znajdującej się w kolumnie Nazwy. A następnie wprowadź nową nazwę kolumny.

W tym przykładzie użyta nazwa kolumny to Revenue . Wprowadź każdą funkcję, aby uzyskać wartości każdego wiersza w kolumnie Uzyskaj przychód.

Kliknięcie miejsca w komórce wiersza w kolumnie Temp spowoduje wyświetlenie podglądu zagnieżdżonej tabeli pod okienkiem podglądu. Zobaczysz, że każdy wiersz ma wartość tabeli zewnętrznej.

Używanie zmiennych do przechowywania wartości

Drugą metodą przekształcania zagnieżdżonych obiektów jest użycie zmiennych do przechowywania wartości. Najpierw dodaj niestandardową kolumnę i wprowadź Temp2 jako nazwę kolumny. W przypadku formuły użyj wyrażenia let do przechowywania wartości w zmiennych.

Wprowadź let , a następnie wpisz nazwę zmiennej. W tym przykładzie nazwa zmiennej to myR . Następnie porównaj zmienną z kolumną Get Revenue. Następnie wprowadź klauzulę in i użyj funkcji Table.AddColumn , aby dodać kolumnę do zagnieżdżonej tabeli.

Jako pierwszy parametr funkcji wprowadź kolumnę Nazwy zawierającą zagnieżdżone tabele. Następnie nazwij nową kolumnę Przychód. Użyj każdego , aby wywołać zmienne każdego wiersza.

Jeśli klikniesz spację wewnątrz komórki wiersza w kolumnie Temp2, zobaczysz, że pobrała ona wartości z zewnętrznej tabeli.

Innym typowym scenariuszem jest wyszukiwanie, w którym nie ma klucza udostępnionego. W tym scenariuszu zostaną użyte 2 przykładowe tabele: tabela kategorii i tabela produktów .

Użyj tabeli kategorii jako zapytania przemieszczającego, aby uzupełnić atrybuty tabeli wymiarów produktu. Ponieważ nie ma klucza współdzielonego, nie można przeprowadzić scalania. Pierwszą rzeczą do zrobienia jest zagnieżdżenie pełnej tabeli kategorii w zapytaniu tabeli produktów za pomocą Dodaj niestandardową kolumnę.

Dodaj niestandardową kolumnę i wpisz Kategoria jako nazwę kolumny. Następnie w formule niestandardowej wywołaj zapytanie o kategorię.

Następnie zagnieździłeś teraz pełną tabelę kategorii w każdym wierszu tabeli produktów.

Gdy klikniesz spację w komórce wiersza w kolumnie Kategoria, zobaczysz pełną tabelę kategorii w każdym wierszu.

Pobieranie pojedynczej wartości

Jeśli chcesz pobrać tylko jedną wartość, musisz odfiltrować tabelę kategorii do pojedynczego wiersza, w którym część ciągu z produktu pasuje do grupy produktów w tabeli kategorii.

Otwórz okno dialogowe Kolumna niestandardowa, naciskając przycisk koła zębatego obok opcji Dodano niestandardowe w okienku Zastosowane kroki.

W formule dodaj funkcję Table.SelectRows . Dla pierwszego parametru używana jest tabela kategorii. Drugi parametr używał funkcji Text.Contains jako warunku. W przypadku tabeli wewnętrznej ta funkcja sprawdza, czy tekst zawiera część ciągu produktu w tabeli zewnętrznej.

Podczas uzyskiwania dostępu do tabeli zewnętrznej zauważysz, że dodatek Power Query dodaje słowo kluczowe each wewnątrz paska formuły. Jest to funkcja jednoargumentowa, która przyjmuje podkreślenie ( _ ) jako zmienną bezimienną. Ponieważ jednak uzyskujesz dostęp tylko do kolumny, można ją pominąć.

Jeśli klikniesz spację w komórce wiersza w kolumnie Kategoria, zobaczysz teraz pojedynczy wiersz w każdej z zagnieżdżonych tabel.

Stamtąd możesz wyodrębnić wartość dla kategorii za pomocą kombinacji operatorów dostępu do wiersza i pola.

Wróć do okna dialogowego Kolumna niestandardowa i dodaj operatory w formule. Aby uzyskać pierwszy wiersz tabeli, wprowadź 0 w zestawie nawiasów klamrowych. Następnie wpisz nazwę pola Kategoria w zestawie nawiasów kwadratowych.

Aby zabezpieczyć rozwiązanie na przyszłość, możesz na końcu dodać znak zapytania ( ? ), aby zmienić zachowanie nieznalezione ze zwracania błędu na wartość null.

Jak działa edytor zapytań w LuckyTemplates
M Language i Power Query Editor w LuckyTemplates

Wniosek

Środowiska i wyrażenia zagnieżdżone pomagają zapytaniu wydobyć określone wartości z kodów w celu uzyskania pożądanych wyników. Świadomość ich jest ważna podczas pisania funkcji , ponieważ kiedy je zrozumiesz, będziesz w stanie rozwiązywać bardziej zaawansowane i złożone scenariusze.

Melisa