W świecie controllingu coraz większe znaczenie ma automatyzacja procesów raportowych i analitycznych. Jednym z narzędzi wspierających takie działania jest Power Query – zaawansowany edytor zapytań dostępny w Excelu i Power BI. Poniższy kod w języku M, czyli języku skryptowym Power Query, przedstawia praktyczne zastosowanie tego narzędzia w analizie danych sprzedażowych na potrzeby controllingu.
Poniższy skrypt w Power Query automatycznie wczytuje wszystkie pliki Excel z folderu, których nazwa zaczyna się od „sales_2025_”
i kończy na „.xlsx”. Następnie z każdego pliku pobiera dane z wybranych arkuszy („sales” lub „Sheet1”), standaryzuje ich strukturę (kolumny: Data, Region, Kanał, Produkt, Netto, Ilość, Kontrahent), sprawdza poprawność nagłówków i konwertuje dane
do odpowiednich typów. Na końcu dodaje kolumnę „RokMiesiąc”, która ułatwia analizę danych w ujęciu czasowym.
Skrypt idealnie nadaje się do automatyzacji analizy danych sprzedażowych w controllingu.
Opis działania kodu
let
Source = Folder.Files("C:\SCIEZKA\DO\FOLDERU"),
Filtered = Table.SelectRows(Source, each Text.StartsWith([Name], "sales_2025_") and Text.EndsWith([Name], ".xlsx")),
AddContent = Table.AddColumn(Filtered, "ContentTbl", each Excel.Workbook([Content], true)),
Expand1 = Table.ExpandTableColumn(AddContent, "ContentTbl", {"Name","Data"}, {"SheetName","Data"}),
KeepRows = Table.SelectRows(Expand1, each [SheetName] = "sales" or [SheetName] = "Sheet1"),
ExpandData = Table.ExpandTableColumn(KeepRows, "Data", {"Column1","Column2","Column3","Column4","Column5","Column6","Column7"}, {"Data","Region","Kanał","Produkt","Netto","Ilość","Kontrahent"}),
HasExpected = List.Count(List.Intersect({Table.ColumnNames(ExpandData), {"Data","Region","Kanał","Produkt","Netto","Ilość","Kontrahent"}}))>0,
Base = if HasExpected then ExpandData else Table.PromoteHeaders(ExpandData, [PromoteAllScalars=true]),
T1 = Table.TransformColumnTypes(Base, {{"Data", type date}, {"Netto", type number}, {"Ilość", Int64.Type}, {"Region", type text}, {"Kanał", type text}, {"Produkt", type text}, {"Kontrahent", type text}}),
AddYM = Table.AddColumn(T1, "RokMiesiąc", each Date.ToText([Data],"yyyy-MM"), type text)
in
AddYM
1. Automatyczne wczytywanie plików
Linia:
Source = Folder.Files("C:\SCIEZKA\DO\FOLDERU")
odpowiada za wczytanie wszystkich plików znajdujących się w podanym folderze.
2. Filtrowanie po nazwie pliku
Filtered = Table.SelectRows(Source, each Text.StartsWith([Name], "sales_2025_") and Text.EndsWith([Name], ".xlsx"))
Z całej listy wybierane są tylko te pliki, których nazwy zaczynają się od sales_2025_ i kończą się na .xlsx. Pozwala to np. analizować tylko dane sprzedażowe z danego roku.
3. Odczyt arkuszy i zawartości
AddContent = Table.AddColumn(Filtered, "ContentTbl", each Excel.Workbook([Content], true))
Z każdego pliku wczytywana jest jego zawartość jako tabela arkuszy.
4. Rozwijanie tabel i wybór arkuszy
KeepRows = Table.SelectRows(Expand1, each [SheetName] = "sales" or [SheetName] = "Sheet1")
Wybierane są tylko dane z określonych arkuszy (np. „sales” lub „Sheet1”).
5. Standaryzacja danych
Dzięki funkcji Table.ExpandTableColumn, dane z arkuszy są rozwijane do jednej tabeli z konkretnymi kolumnami: Data, Region, Kanał, Produkt, Netto, Ilość, Kontrahent.
6. Sprawdzenie poprawności danych
HasExpected = List.Count(List.Intersect({Table.ColumnNames(ExpandData), {"Data","Region","Kanał","Produkt","Netto","Ilość","Kontrahent"}}))>0
Kod weryfikuje, czy wszystkie wymagane kolumny są obecne. Jeśli nie, promuje pierwszy wiersz jako nagłówki.
7. Typowanie danych i dodanie kolumny pomocniczej
T1 = Table.TransformColumnTypes(...)
AddYM = Table.AddColumn(T1, "RokMiesiąc", each Date.ToText([Data],"yyyy-MM"), type text)
Typy danych są przypisywane zgodnie z ich zawartością, a dodatkowo tworzona jest kolumna „RokMiesiąc” na potrzeby analizy trendów czasowych.
Zastosowanie w controllingu
Ten kod jest niezwykle użyteczny w codziennej pracy kontrolera finansowego. Dzięki niemu:
- Automatyzujesz pobieranie danych z wielu plików Excela bez konieczności ręcznego otwierania każdego z nich.
- Ujednolicasz strukturę danych, co pozwala na szybkie połączenie ich w jeden raport.
- Zwiększasz bezpieczeństwo analizy, eliminując ryzyko błędów przy kopiowaniu danych między plikami.
- Przygotowujesz dane do dalszej analizy np. w Power BI lub za pomocą przestawnych tabel w Excelu.
Tego typu skrypty mogą być łatwo dostosowane do innych potrzeb – np. analiz zakupów, kosztów operacyjnych czy budżetowania.
Zastosowanie Power Query w controllingu to krok w stronę nowoczesnego, zautomatyzowanego zarządzania danymi. Dzięki prostemu, lecz elastycznemu kodowi jak ten powyżej, kontrolerzy mogą skupić się na analizie i rekomendacjach zamiast na żmudnym przetwarzaniu danych.
