Archiwum

Tworzenie tabeli "kalendarza" za pomocą funkcji w Power BI

utworzone przez Adam Kopeć | mar 28, 2017 | PowerBI | 3 Komentarze

Czasami przy pracy z danymi wczytywanymi do Power BI przydaje nam się tabela z rozpisanymi na różne sposób datami, gdzie oprócz samej daty mamy jeszcze np.: kolumny, z numerem roku, miesiąca, dnia, kwartału lub połączonych tych informacji. Żeby nie musieć tworzyć takiej tabeli ręcznie różni programiści stworzyli kod funkcji w języku M, żeby zautomatyzować to zadanie.

Ja korzystam z kodu Matta Massona, ze strony (dowiedziałem się o nim od Jona Acampora):
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

Który odrobinę zmodyfikowałem (głównie zmieniłem nazwy kolumn), żeby bardziej odpowiadał polskim ustawieniom.

let CreateDateTable = (DataPoczątkowa as date, DataKońcowa as date, optional Język as nullable text) as table =>
  let
    DayCount = Duration.Days(Duration.From(DataKońcowa - DataPoczątkowa)),
    Source = List.Dates(DataPoczątkowa,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Data"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Rok", each Date.Year([Data])),
    InsertQuarter = Table.AddColumn(InsertYear, "Kwartał", each Date.QuarterOfYear([Data])),
    InsertMonth = Table.AddColumn(InsertQuarter, "Miesiąc", each Date.Month([Data])),
    InsertDay = Table.AddColumn(InsertMonth, "Dzień", each Date.Day([Data])),
    InsertDayInt = Table.AddColumn(InsertDay, "DataLiczba", each [Rok] * 10000 + [Miesiąc] * 100 + [Dzień]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "NazwaMiesiąca", each Date.ToText([Data], "MMMM", Język), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MiesiącKalendarz", each (try(Text.Range([NazwaMiesiąca],0,3)) otherwise [NazwaMiesiąca]) & " " & Number.ToText([Rok])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "KwartałKalendarz", each "K" & Number.ToText([Kwartał]) & " " & Number.ToText([Rok])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "NrDniaTygodnia", each Date.DayOfWeek([Data],Day.Monday)+1),
    InsertDayName = Table.AddColumn(InsertDayWeek, "NazwaDniaTygodnia", each Date.ToText([Data], "dddd", Język), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "KoniecTygodnia", each Date.EndOfWeek([Data]), type date)
  in
    InsertWeekEnding
in
  CreateDateTable

Jak skorzystać z kodu funkcji w PowerBI?

Żeby skorzystać z tej funkcji w PowerBI musisz stworzyć najpierw puste zapytanie (karta Narzędzia główne -> rozwiń polecenie Pobierz dane -> wybierz z listy Puste zapytanie)

Następnie w edytorze zapytań wybierz polecenie Edytor zaawansowany z karty Narzędzia główne.

W oknie edytora zaawansowanego wklejasz skopiowany z początku wpisu kod, a następnie zatwierdzasz go przyciskiem Gotowe.

Teraz przy aktywnym tym zapytaniu zobaczysz okno wprowadzania parametrów funkcji.

DataPoczątkowa – to data, od której będzie zaczynał się stworzony kalendarz
DataKońcowa – to data, na której będzie kończył się stworzony kalendarz
Język – parametr opcjonalny, który decyduje w jakim języku są prezentowane dane np.: nazwy miesiąca, czy dni tygodnia. Jeśli nie wypełnisz tego pola, to będą brane pod uwagę ustawienia aplikacji PowerBI. Możesz zobaczyć szczegóły tego parametru na stronie:
[MS-LCID]: Windows Language Code Identifier (LCID) Reference

Wystarczy, że wypełnisz to okno parametrami zgodnie ze swoimi potrzebami, a następnie zatwierdzisz wybór przyciskiem Wywołaj.

Żeby edytor zapytań stworzył dla Ciebie całą tabelę danych. Pamiętaj jeszcze załadować ją do aplikacji PowerBI Desktop (polecenie Zamknij i zastosuj na karcie Narzędzia główne).

Chcesz lepiej poznać PowerBI wejdź na stronę mojego kursu:
https://exceliadam.pl/kursy/power-bi

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak cofnąć makro — odcinek #20

utworzone przez Adam Kopeć | mar 9, 2017 | Excel VBA | 2 Komentarze

Ogólnie jeśli chcesz Twoje makro ruszy arkusz, czy też dane w komórkach, to również wyczyści bufor cofania i nie będziesz mógł cofnąć, ani czynności, które wykonało makro, ani czynności, które wcześniej wykonywałeś — Ctrl + Z nie zadziała.

Jeśli Twoje makro nie rusza arkusza, np: wyświetla komunikat za pomocą funkcji Msgbox, to bufor cofania nie jest czyszczony i dalej możesz cofnąć swoje wcześniejsze czynności za pomocą skrótu klawiszowe.

Niektórzy chcieliby móc cofać swoje makra, dlatego został stworzony kod, który wykorzystuje pewną sztuczkę, a mianowicie w swoim makrze na samym jego początku dodajesz linijkę kodu, która zapisuje Twój plik:
ActiveWorkbook.Save

Następnie jeśli Twoje makro zadziałało niepoprawnie uruchamiasz makro, które powinieneś przechowywać w innym pliku np: skoroszycie makr osobistych, które pobiera nazwę Twojego pliku (aktywnego pliku), zamyka go bez zapisywania, a następnie ponownie uruchamia:

Sub Cofanie()
Dim AktualnyArkusz As String
'Zapisujemy nazwę aktywnego skoroszytu/pliku
AktualnyArkusz = ActiveWorkbook.FullName
'zamykamy go bez zapisywania zmian
ActiveWorkbook.Close SaveChanges:=False
'ponownie go otwieramy
Workbooks.Open Filename:=AktualnyArkusz
End Sub

Czyli symuluje to co musiałbyś zrobić ręcznie, żeby odzyskać swoje dane 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak wstawić etykiety danych z komórek arkusza do wykresu Excel 2013 — Porada #289

utworzone przez Adam Kopeć | mar 6, 2017 | Excel | 2 Komentarze

Od Excela 2013 pojawia się bardzo ciekawa funkcjonalność jeśli chodzi o etykiety danych na wykresie, a mianowicie możliwość ich pobrania z komórek arkusza. Pokażemy tą możliwość na przykładzie wykresu słupkowego.

W Excelu 2013, żeby dodać etykiety danych, należy kliknąć w zielony plus, a następnie wybrać odpowiedni element wykresu, który chcemy dodać. W naszym przykładzie będą to etykiety na końcu zewnętrznym.

Po ich dodaniu pokażemy jeszcze jedną sztuczkę dostępną od Excela 2013 — zaznaczymy pojedynczą etykietę danych i przytrzymując klawisz Shift (żeby pozostała na tej samej wysokości) przesuniemy ją myszką w bok. Następnie zmienimy parę parametrów tej etykiety, żeby było widoczne, że jest inna od pozostałych etykiet danych.

Teraz naciskamy Ctrl + 1 (wciąż zaznaczona jest zmieniana przez nas etykieta danych) i w zakładce Opcje etykiet klikamy w przycisk Sklonuj bieżącą etykietę danych.

Po tej operacji wszystkie etykiety będą miały takie samo formatowanie jak nasza zmieniona etykieta.
Teraz chcemy dodać dane do etykiet z zakresu komórek. Żeby to zrobić musimy mieć zaznaczone wszystkie etykiety i wtedy w zakładce Opcje etykiet będzie się wyświetlała opcja Wartości z komórek. Wystarczy, że oknie, które się pojawi, zaznaczysz zakres komórek, z których chcesz pobrać wartości do etykiet danych.

Ewentualnie powinniśmy odznaczyć informacje, których nie chcemy, żeby etykiety pokazywały. Teraz mamy etykiety pokazujące wartości z komórek, które są tak samo sformatowane jak te komórki, poza drobnym wyjątkiem, że nie kopiują koloru czcionki.

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak znaleźć komórkę z odwołaniem cyklicznym — porada #288

utworzone przez Adam Kopeć | mar 3, 2017 | Excel | 4 Komentarze

Odwołania cykliczne, czy też iteracyjne, to takie odwołania, które odwołują się do komórki, w której same się znajdują. Najczęściej są tworzone nieumyślnie — powoduje to problem, gdyż formuła odwołująca się do wartości, którą sama zwraca przeważnie działa niepoprawnie.

Jeśli Excel wyświetli Ci komunikat o niepoprawnym działaniu formuł cyklicznych,

to żeby je szybko odnaleźć wejdź na kartę Formuły, odszukaj polecenie Sprawdzanie błędów rozwiń je i wybierz Odwołania cykliczne.

Wystarczy, że klikniesz odwołanie wskazujące do komórki z odwołaniem cyklicznym, a Excel Cię tam przeniesie.
Polecenie odszukujące odwołania cykliczne będzie aktywne tylko wtedy kiedy masz wyłączone obliczenia iteracyjne (cykliczne) w swoim pliku. Wystarczy, że wejdziesz do karty Plik, polecenie opcje i na zakładce formuły odnajdziesz odpowiedni checkbox.