W ramach rozwijania Power Query Microsoft stale zwiększa możliwości narzędzia o nazwie Kolumna z przykładów. Opiszę kilka ciekawych przykładów, jak opcję tę można wykorzystać w sposób niestandardowy i jakie nowe możliwości pojawiły się niedawno. Jeśli o kolumnie z przykładów czytasz pierwszy raz, zerknij na podstawy pracy z kolumną z przykładów w Excel i Power BI Power Query. W skrócie, jeśli Szef chce, aby zrobić coś „piorunem”, Kolumna z przykładów jest najlepszym wyjściem.

Kolumna z przykładów w Power Query

Funkcja ta pozwala w bardzo szybki sposób stworzenie nowych kolumn w następujących przypadkach:

• gdy wiem jakie dane w kolumnie chcę uzyskać, ale nie wiem jakimi funkcjami oraz jakich przekształceń dokonać
• gdy wiem jakich przekształceń mam dokonać, ale nie wiem gdzie mogę je odnaleźć na wstążce.

Funkcję można znaleźć w Edytorze zapytań > Dodaj kolumnę > Ogólne > Kolumna z przykładów.

Kryją się pod nią dwie możliwości: z wszystkich kolumn, z zaznaczenia (domyślną, bez rozwijania listy wyboru jest Ze wszystkich kolumn).

Po wyborze niezależnie której z opcji, pojawi się okno dodawania kolumny, kolumna o nazwie Kolumna1, w której będę wpisywać przykłady. Następnie Power BI użyje ich do stworzenia reguł i przekształceń pasujących do podanego przykładu. Jednocześnie wykonywane kroki są zapisywane w Edytorze Zapytań, więc zostaną zastosowane do zapytania w zarejestrowanej kolejności.

Aby wpisać pierwszą pozycję do kolumny z przykładów wystarczy dwuklik. W zależności od wybranej opcji Ze wszystkich kolumn lub Z zaznaczenia wyświetlona zostanie wyświetlona lista z opcjami do wyboru (dłuższa – dla wszystkich kolumn, krótsza – z zaznaczenia).

Po wpisaniu przykładu w pierwszej komórce Kolumna1 > Enter w tworzonej kolumnie, zostaną wstawione wartości przewidywane przez Power Query. Zostanie również wyświetlona formuła , która tych przekształceń dokona. Aby zaakceptować transformację wybieram > OK.

Jakie przekształcenia mogą by wykonane podczas tworzenia kolumny z przykładów w Edytorze zapytań?

• Odwołanie do określonej kolumn (jej przycięcie, zmiana wielkości liter itp.)
• Przekształcenia liczbowe (średnia, zliczanie wartości, zliczanie wartości unikatowych, minimum, maximum, mediana, zasilanie itp)
• Przekształcenia tekstu (Text.Start, Text.End, Text.BeforeDelimiter itp.)
• Przekształcenia daty (Date.Day, Date.DayOfWeek, Date.Year itp)
• Przekształcenia czasu (Time.Hour, Time.Minute, Time.Second)
• Data/Godzina przekształcenia (odejmowanie dni i godzin, łączenie daty i godziny itp.)

Opcje zastosowania

Mapowanie wartości w nowej kolumnie

W nowej kolumnie tworzę odpowiedniki dla wartości występujących w oryginalnych kolumnach.

Dla kolumny o nazwie Priorytet zamówienia tworzę nową kolumnę, w której umieszczę odpowiednik literowy dla każdego z priorytetów:

– krytyczny – E

– wysoki – D

– średni – C

– niski – B

– nieokreślony – A

Zaznaczam kolumnę Priorytet zamówienia > Dodaj kolumnę > Kolumna z przykładów > Z zaznaczenia

W pierwszą komórkę nowej  kolumny wpisuję odpowiednią literę, zgodnie z wcześniejszym słownikiem, akceptuję Enterem , wprowadzam literę do kolejnej komórki. Po akceptacji cała kolumna zostaje uzupełniona wprowadzonymi do tej pory wartościami. Na pasku pojawia się również propozycja formuły tworzącej to przekształcenie.

Jak widać w wielu komórkach nowej kolumny, są wciąż nulle, co oznacza że dla nich obecnie stworzona formuła nie działa. Zatem uzupełniam pozostałe komórki, odpowiadające kolejnym typom priorytetu. Całość akceptuję Ctrl + Enter.

Widać (częściowo, bo jest zbyt długa)  już ostateczną formułę dla tego przekształcenia oraz wypełnioną całą nową kolumnę. Dla umieszczenia kolumny w Zapytaniu wybieram OK.

Kolumna warunkowa

Pozwala na dodanie nowej kolumny o niejednolitych zakresach, opartej na kolumnie wejściowej. Stworzę taką kolumnę, na przykładzie Miasta, gdzie będę oddzielał Warszawę od reszty miast. Poniżej widok, jaka została generowana formuła dla nowej kolumny. Prawidłowo!

Uzupełnienie pustych komórek (null)

Zaznaczam kolumny Rodzaj transportu 2-3, w których posiadam nulle, ale wiem, że powinny one tworzyć jedną spójną, pełną wartości kolumnę. Wykonuję kolejne kroki > Dodaj kolumnę > Kolumna z przykładów > Z zaznaczenia. W nowej kolumnie wprowadzam prawidłowe wartości.

W trakcie tworzenia nowej kolumny, trzeba w kilku miejscach wprowadzić prawidłowe wartości, tak aby algorytm Power BI wiedział, że z trzech kolumn będzie powstawała nowa.

Tak wygląda nowo skonstruowana kolumna z trzech innych oraz formuła, która tworzy to przekształcenie.

Tworzenie przedziałów

Wybieram kolumnę Wielkość zamówienia > Dodaj kolumnę > Kolumna z przykładów > Z zaznaczenia. Będę tworzyć przedziały, w pierwszej komórce tworzonej kolumny podaję przykładowy przedział „15 do 25”. Na tej podstawie zostaje uzupełniona cała kolumna, a nawet jej nazwa zmienia się na „Zakres”.