Power Query świetnie radzi sobie z kalkulacjami w pojedynczym wierszu. Możemy zaznaczyć kolumny i poprzez Dodaj kolumnę „wyklikać” potrzebną transformację (np. dodaj) lub utworzyć bardziej złożony wzór poprzez Kolumnę niestandardową. Jednak co zrobić w przypadku, gdy chcę się odnieść do wiersza poniżej? Coś, co w Excelu przychodzi z olbrzymią łatwością poprzez adresowanie komórek, w Power Query może stać się nie lada wyzwaniem.
Przygotowanie danych w tabeli Excel
Przykład rozpoczniemy od zbioru danych, w którym znajdziemy 2 kolumny: Data i Sprzedaż.
Cel: obliczyć dynamikę dzień do dnia.
W komórkach Excel formuła nie sprawi nam większych problemów.
Jak podobny efekt osiągnąć w Excel Power Query?
Krok 1: Utwórz kolumnę indeksu od 0
Kolumnę indeksu znajdziemy na karcie Przekształć > Kolumna indeksu > od 0. Zaczyna się od 0, gdyż domyślną liczbą startową w formułach Power Query jest 0, a nie jak w zwykłym Excelu 1.
Krok 2: Utwórz kolumnę indeksu od 1
Krok 3: Scal zapytanie z samym sobą
Na karcie Narzędzia główne wybierz Scalanie, a następnie wskaż kolumnę indeksu od 0 jako klucz pierwszej tabeli i kolumnę indeksu od 1 jako klucz drugiej tabeli.
Po rozpakowaniu otrzymamy szukaną wartość z wiersza niżej.
Krok 4: Zbuduj formułę w Power Query
Ostatni krok to obliczenie dynamiki za pomocą Kolumny niestandardowej.
Plik do pobrania
Tu możesz pobrać plik Excel Zmiana między wierszami w Power Query i zajrzeć do gotowego zapytania.
Witam
A jak wykonać takie scalanie dla dat? Chodzi o obliczenie czasu pomiędzy wykonanymi zadaniami. Mamy:
Zadania1 Czas rozpoczęcia 2024-01-01 06:00:00 Czas zakończenia 2024-01-01 06:15:21
Zadanie2 Czas rozpoczęcia 2024-01-01 06:22:38 Czas zakończenia 2024-01-01 06:26:27
Zadanie3 Czas rozpoczęcia 2024-01-01 06:55:00 Czas zakończenia 2024-01-01 07:02:02 itd
Należy obliczyć czas Czas rozpoczęcia 2024-01-01 06:22:38 – Czas zakończenia 2024-01-01 06:15:21 itd
Cześć Jakub, oto moja propozycja:
1. Posortuj zadania wg daty rosnąco.
2. Dodaj kolumnę indeksu dwa razy – Indeks1 od 0 i Indeks2 od 1.
3. Scal zapytanie z samym sobą, tak żeby kluczem była kolumna Indeks2 (w górnej tabeli) i Indeks1 (w dolnej tabeli).
4. Rozpakuj scalanie i pobierz datę z wiersza poniżej.
5. Oblicz różnicę między czasami: Dodaj kolumnę > Data > Odejmij dni lub > Godzina > Odejmij.
Scalanie na sztucznych kolumnach indeksu opisałem w filmie o pobieraniu kursów z NBP: https://skuteczneraporty.pl/blog/jak-automatycznie-pobierac-do-excela-kursy-walut-z-nbp-lekcja-18-w-kursie-pobieranie-danych-w-excelu-dla-finansistow/
Bardzo fajnie, przyjaźnie i prosto to pokazałeś. Dziękuję Ci za to, że dzielisz się wiedzą.
Dzięki, Jola!