Webinarium Microsoft Excel Power Query
Вставка
- Опубліковано 30 вер 2024
- Poznaj narzędzie Power Query w Excelu.
💚 Plik wykorzystywany podczas webinarium znajdziesz pod linkiem ▶ expose.pl/webi...
💚 Spodobało się? Skorzystałaś/eś? Miło nam będzie jak zostawisz 5 ⭐ i dobre słowo w wizytówce Google ▶ g.page/r/CZ1uu...
💚 Chcesz więcej? Zapraszamy na nasze szkolenia online ▶ online.expose.pl/ oraz szkolenia stacjonarne/zdalne na żywo ▶ expose.pl/
Wszystko bardzo fajnie ale w ostatnim zadaniu wkradł się błąd. Nie w każdym oddziale są dane dla każdego miesiąca tj. w oddziale B brakuje maja i czerwca. Stąd krokiem "Przefiltrowanie wierszy 2" przypisujemy dane z miesięcy lipiec, sierpień itd. do maja, czerwca... jakieś rozwiązanie na to?
Rzeczywiście jest problem. Błąd wynika z faktu, że zbierając dane bezpośrednio z arkuszy, kolumny są dodawane w kolejności wystąpienia. Najprostszym rozwiązaniem jest umieścić dane w odrębnych tabelach (ważne, aby pierwszy wiersz był traktowany jako nagłówki). Później pobieramy dane ze skoroszytu, bierzemy wszystkie obiekty, tak jak na webinarze, z tym, że filtrując nie szukany ciągów tekstowych zawartych w nazwach arkuszy, a nazwach tabel. Dzięki takiemu rozwiązaniu kolumny zostaną przyporządkowane na podstawie nazw z nagłówków. Dalsze kroki bez zmian.
Próbowałem i w żaden sposób nie wychodzi czy istnieje szansa na szersze wyjaśnienie lub plik z zapisaną historią kroków w zapytaniu?
@@mirosawtopolski1563
tabele tworzymy na zakresach i nazwami je: oddziałA, oddziałB, oddziałC (tak jest w moim przykładzie); pierwszy wiersz jako nagłówek; kolumna która nie ma wpisanego nagłówka to Kolumna1
poniższy kod należy wkleić w PowerQuery do Edytora Zaawansowanego (karta Widok), zmieniając ścieżkę z pierwszego kroku, na położenie własnego pliku
let
Źródło = Excel.Workbook(File.Contents("C:\Users\Karol\Desktop\Przykłady_PQ.xlsx"), null, true),
#"Tekst pisany małymi literami" = Table.TransformColumns(Źródło,{{"Item", Text.Lower, type text}}),
#"Przefiltrowano wiersze" = Table.SelectRows(#"Tekst pisany małymi literami", each ([Kind] = "Table")),
#"Przefiltrowano wiersze1" = Table.SelectRows(#"Przefiltrowano wiersze", each Text.Contains([Item], "oddział")),
#"Rozwinięty element Data" = Table.ExpandTableColumn(#"Przefiltrowano wiersze1", "Data", {"Kolumna1", "Sty", "Lut", "Mar", "Kwi", "Maj", "Cze", "Lip", "Sie", "Wrz", "Paź", "Lis", "Gru", "RAZEM"}, {"Kolumna1", "Sty", "Lut", "Mar", "Kwi", "Maj", "Cze", "Lip", "Sie", "Wrz", "Paź", "Lis", "Gru", "RAZEM"}),
#"Usunięto kolumny" = Table.RemoveColumns(#"Rozwinięty element Data",{"RAZEM", "Item", "Kind", "Hidden"}),
#"Dodano kolumnę warunkową" = Table.AddColumn(#"Usunięto kolumny", "Niestandardowe", each if [Sty] = null then [Kolumna1] else null),
#"Wypełniono w dół" = Table.FillDown(#"Dodano kolumnę warunkową",{"Niestandardowe"}),
#"Przefiltrowano wiersze2" = Table.SelectRows(#"Wypełniono w dół", each ([Kolumna1] "Koszty" and [Kolumna1] "KOSZTY RAZEM" and [Kolumna1] "Sprzedaż" and [Kolumna1] "SPRZEDAŻ RAZEM" and [Kolumna1] "ZYSK BRUTTO")),
#"Zmieniono kolejność kolumn" = Table.ReorderColumns(#"Przefiltrowano wiersze2",{"Name", "Kolumna1", "Niestandardowe", "Sty", "Lut", "Mar", "Kwi", "Maj", "Cze", "Lip", "Sie", "Wrz", "Paź", "Lis", "Gru"}),
#"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Zmieniono kolejność kolumn",{{"Name", "Oddział"}, {"Kolumna1", "Produkt"}, {"Niestandardowe", "Kategoria"}}),
#"Anulowano przestawienie innych kolumn" = Table.UnpivotOtherColumns(#"Zmieniono nazwy kolumn", {"Oddział", "Produkt", "Kategoria"}, "Atrybut", "Wartość"),
#"Zmieniono nazwy kolumn1" = Table.RenameColumns(#"Anulowano przestawienie innych kolumn",{{"Atrybut", "Miesiąc"}})
in
#"Zmieniono nazwy kolumn1"
@@exposepl Działa:) Dziękuję
Wczoraj obejrzałem ten filmik i też dostrzegłem błąd w zadaniu nr 5. Jak się okazuje, ktoś był szybszy (i to o cztery miesiące).
Co do rozwiązania problemu, to nie trzeba tworzyć tabel (jak napisał Autor). Wystarczy napisać funkcję na przykładzie danych z jednego arkusza, i odwołać się do niej w zapytaniu.
Poniższa funkcja "do wyniku" to tylko lekko zmodyfikowane zapytanie "Przykłady_PQ.xlsx" prezentowane w szkoleniu ("Webinarium Microsoft Excel Power Query").
Poniżej przykładowe rozwiązanie (u mnie działa).
Uwaga: Zamiast "C:\Users\.............tutaj...lokalizacja...Twojego...pliku...i...jego...nazwa...z...rozszerzeniem................." wpisz lokalizację i nazwę swojego pliku (pamiętaj o rozszerzeniu pliku i o cudzysłowach).
1. Funkcja o nazwie np. "do wyniku":
(Oddział as text)=>
let
Źródło = Excel.Workbook(File.Contents("C:\Users\.............tutaj...lokalizacja...Twojego...pliku...i...jego...nazwa...z...rozszerzeniem................."), null, true),
#"Przefiltrowano wiersze1" = Table.SelectRows(Źródło, each ([Kind] = "Sheet")),
#"Przefiltrowano wiersze2" = Table.SelectRows(#"Przefiltrowano wiersze1", each [Item] = Oddział),
#"Rozwinięty element Data" = Table.ExpandTableColumn(#"Przefiltrowano wiersze2", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14"}),
#"Usunięto kolumny" = Table.RemoveColumns(#"Rozwinięty element Data",{"Item", "Kind", "Hidden"}),
#"Dodano kolumnę warunkową1" = Table.AddColumn(#"Usunięto kolumny", "Niestandardowe", each if [Data.Column1] = null then "oddział" else [Name]),
#"Usunięto kolumny1" = Table.RemoveColumns(#"Dodano kolumnę warunkową1",{"Name"}),
#"Nagłówki o podwyższonym poziomie" = Table.PromoteHeaders(#"Usunięto kolumny1", [PromoteAllScalars=true]),
#"Dodano kolumnę warunkową" = Table.AddColumn(#"Nagłówki o podwyższonym poziomie", "Niestandardowe", each if [Sty] = null then [Column1] else null),
#"Wypełniono w dół" = Table.FillDown(#"Dodano kolumnę warunkową",{"Niestandardowe"}),
#"Przefiltrowano wiersze" = Table.SelectRows(#"Wypełniono w dół", each ([Column1] null and [Column1] "Koszty" and [Column1] "KOSZTY RAZEM" and [Column1] "Sprzedaż" and [Column1] "SPRZEDAŻ RAZEM" and [Column1] "ZYSK BRUTTO")),
#"Anulowano przestawienie innych kolumn" = Table.UnpivotOtherColumns(#"Przefiltrowano wiersze", {"oddział", "Column1", "Niestandardowe"}, "Atrybut", "Wartość"),
#"Przefiltrowano wiersze3" = Table.SelectRows(#"Anulowano przestawienie innych kolumn", each ([Atrybut] = "Sty" or [Atrybut] = "Lut" or [Atrybut] = "Mar" or [Atrybut] = "Kwi" or [Atrybut] = "Maj" or [Atrybut] = "Cze" or [Atrybut] = "Lip" or [Atrybut] = "Sie" or [Atrybut] = "Wrz" or [Atrybut] = "Paź" or [Atrybut] = "Lis" or [Atrybut] = "Gru")),
#"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Przefiltrowano wiersze3",{{"Column1", "Produkt"}, {"Niestandardowe", "Kategoria"}, {"Atrybut", "Miesiąc"}})
in
#"Zmieniono nazwy kolumn"
2. Zapytanie o nazwie "wynik":
let
Źródło = Excel.Workbook(File.Contents("C:\Users\.............tutaj...lokalizacja...Twojego...pliku...i...jego...nazwa...z...rozszerzeniem................."), null, true),
#"Przefiltrowano wiersze1" = Table.SelectRows(Źródło, each ([Kind] = "Sheet")),
#"Przefiltrowano wiersze2" = Table.SelectRows(#"Przefiltrowano wiersze1", each Text.Contains([Item], "oddział")),
#"Usunięto inne kolumny" = Table.SelectColumns(#"Przefiltrowano wiersze2",{"Item"}),
#"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Usunięto inne kolumny",{{"Item", "Oddział"}}),
#"Wywołana funkcja niestandardowa" = Table.AddColumn(#"Zmieniono nazwy kolumn", "do wyniku", each #"do wyniku"([Oddział])),
#"Rozwinięty element do wyniku" = Table.ExpandTableColumn(#"Wywołana funkcja niestandardowa", "do wyniku", {"Produkt", "oddział", "Kategoria", "Miesiąc", "Wartość"}, {"Produkt", "oddział.1", "Kategoria", "Miesiąc", "Wartość"}),
#"Usunięto kolumny" = Table.RemoveColumns(#"Rozwinięty element do wyniku",{"oddział.1"})
in
#"Usunięto kolumny"
W zadaniu 5 jest istotny BŁAD w założenich, ilość i kolejnośc miesięcy w poszczególnych oddzialach jest różna przez co dane sa przypisanych do niewłaściwych miesiecy. A usunięcie columny14 nie usuwa RAZEM z innych oddziałow. Brak kroków dla Tabeli[Data]. Po za tym szkolenie super 💪
Tak, masz rację ;) w jednym z komentarzy już o tym pisaliśmy ;)
Błąd wynika z faktu, że zbierając dane bezpośrednio z arkuszy, kolumny są dodawane w kolejności wystąpienia. Najprostszym rozwiązaniem jest umieścić dane w odrębnych tabelach (ważne, aby pierwszy wiersz był traktowany jako nagłówki). Później pobieramy dane ze skoroszytu, bierzemy wszystkie obiekty, tak jak na webinarze, z tym, że filtrując nie szukany ciągów tekstowych zawartych w nazwach arkuszy, a nazwach tabel. Dzięki takiemu rozwiązaniu kolumny zostaną przyporządkowane na podstawie nazw z nagłówków. Dalsze kroki bez zmian.
Ja dodalem tylko 1 dodatkowy krok, pierwszy wiersz jako nazwy kolum w tabelu Data przed rozwinieciem
#"Przefiltrowano wiersze1" = Table.SelectRows(#"Tekst pisany małymi literami", each Text.Contains([Item], "oddział")),
#"Dodano kolumnę niestandardową" = Table.AddColumn(#"Przefiltrowano wiersze1", "Niestandardowe", each Table.PromoteHeaders([Data], [PromoteAllScalars=true])),
@@marcinmazurek1184 Tak, to również rozwiązuje problem, świetny pomysł! ;)
Pięknie, coraz bardziej przekonuję się do Power Query. Super, że tyle rzeczy da się tym narzędziem ogarnąć. Jeszcze kilka miesięcy temu nie rozróżniałam tabeli od zakresu, nie znałam funkcji Wyszukaj.Pionowo, nie wiedziałam co robi się w Excelu dolarami, w marcu tego roku umiałam obliczyć tylko sumę i procenty. Człowiek całe życie się uczy i teraz jestem właśnie na etapie ćwiczeń z PQ. Dziękuję bardzo za dzielenie się wiedzą.
Cudowne szkolenie - tylko to mlaskanie, które doprowadza do szalu. Korzystajcie z filtrów, błagam.
Genialny materiał, takiego szukałem, a o power query próbowałem wcześniej oglądać inne. Żadnego innego tak przyjemnie mi się nie oglądało. Leci sub. Zdrowia i wolności na Święta, Pozdrawiam
Hej macie może jakieś fajne forum dla power query??
super super sprawa z tym PQ, i wielkie dzieki za Twoja prace. Zawsze dobrze jest nauczyć się czegoś nowego o excelu. Choć nie wykorzystuje tego w pracy, to excel od dziecka mnie sobą wabił. Maży mi sie mieć certyfikat zaawansowanego excela. chociażby dla siebie :)
Świetne szkolenie, nie doceniałem tego narzędzia.
Swego czasu na szkoleniu excel advanced prowadzący wspominał nam o tym i zróbił szybko pokaz, ale jakoś mnie nie zachęcił.
Teraz już wiem, ile straciłem i mam co nadrabiać. Dzięki bardzo ;)
Szok, niedowierzanie. Mamy truskawkę.
Świetne, ciekawe. Gratulacje
Bardzo fajne i przydatne szkolenie:-)
dzięki ;)
Super, dzięki! Bardzo przydatne i zrozumiale wytłumaczone
Super prowadzenie i bardzo przydatne do nauki, gdzie mogę pobrać ten plik do ćwiczeń?
W opisie pod filmem jest link do pobrania pliku 😉
Dzięki! To jeden z materiałów, który wniósł coś coś przydanego do mojego życia :-)
Dzięki :)
Miło nam będzie jak zostawisz 5 ⭐ i dobre słowo w wizytówce Google ▶ g.page/r/CZ1uutYXDYVvEA0
Bardzo dobre.
Mega materiał. Dzięki!
Super dzięki bardzo :)
Kolejne ciekawe Webinarium ułatwiające pracę. Super robota, polecam każdemu kto chce odświeżyć wiedzę lub nauczyć się czegoś nowego.
Panie Karolu, bardzo fajny materiał - dziękuje.
Webinar świetny. Spróbowałem tego na Windows 11 oraz Windows 10 Enterprise z Excel 365. Power Query z zadania 5 działa świetnie z małym, ale irytującym wyjątkiem. Kiedy zapiszę i dodam nową zakładkę (worksheet) do zadania to przy próbie zapisu dostaję komunikat Sharing Violation. Gdzie jest błąd?
Komunikat mówi, że plik na którym pracujesz używany jest jest przez inną aplikację lub innego użytkownika. Nie jest to kwestia Power Query, a samego Excela. Upewnij się, że plik na którym pracujesz nie jest używany przez innych użytkowników, albo inne aplikacje np. OneDrive. Upewnij się też czy pobrany plik jest wypakowany/rozpakowany z archiwum. Można też spróbować zapisać plik w innej lokalizacji - czasami pomaga.
Jak podstawić to pod unikalne ID (połączeń telefonicznych) i zrobić do tego filtrację automatyczną, w której mam listę kontaktów, i powiedzmy ostatnie 30 kontaktów z bilingu?
Jeżeli chcesz wyciągnąć unikalne ID to spróbuj usunąć duplikaty lub zgrupować dane (wtedy musisz jeszcze wybrać jakąś operację agregacji danych).
Jeśli chcesz wyciągnąć ostatnie 30 kontaktów skorzystaj z opcji 'Zachowaj wiersze' - 'Zachowaj pierwsze wiersze' (wcześniej należy odpowiednio posortować dane).
Witam, a czy ktoś mógłby mi pomóc w połaczeniu 2 arkuszy po jedenj wspólnej kolumnie?
a z czym jest konkretnie problem? ;)
Po odświeżeniu dostaje tylko 10 produktów 🤷♂
Przyczyn może być bardzo wiele 😉
rozwiń temat, co dokładnie robisz? to postaramy się pomóc 😉
Dziękuję bardzo za dzielenie się wiedzą.
Super materiał, dzięki!
Cześć, super webbinar :), dziękuję !
Wszystko ekstra, ale co mogę robić żle jeżeli wstawiając kolumnę warunkową wszędzie wyskakuje null? Pozdrawiam
Pewnie ciężko będzie to wyjaśnić. W ostatnim zadaniu już działa, ale gdyby miał Pan pomysł to proszę się nim podzielić.
@@stanisawhinz8250 Sprawdź literówki i wielkość liter - PQ rozróżnia wielkość liter wszędzie.
Witam. W zadaniu drugim po przeniesieniu tabeli do PowerQuery. W kolumnie Kwota u Ciebie dane zostały sformatowane automatycznie do typu Liczba. U mnie natomiast sięto nie dzieje. Ciągle widnieją złotówki przy liczbach. Jest jakiś sposób by wartości w kolumnie Kwota przekonwertowały się na liczby? Pozdrawiam
Sprawdź na jakiej wersji Excela pracujesz? PL czy EN? I jakie masz ustawienia regionalne w systemie? PL/EN/US? Bo wydaje mi się, że to może być problemem.
Świetny content! Dziękuję.
dobrze się to ogląda
Świetny materiał, wiele tłumaczy
Dzięki! Miło nam będzie jak zostawisz 5 ⭐ i/lub dobre słowo w wizytówce Google ▶ g.page/r/CZ1uutYXDYVvEA0
Świetny webinar
Dzięki! :) Miło nam będzie jak zostawisz 5 ⭐i dobre słowo w wizytówce Google ▶ g.page/r/CZ1uutYXDYVvEA0
23:48 robię dokładnie tak samo jak Pan, jednakże sumuje mi kwote za jedną sztuke, nie za wszystkie.
Jeśli możesz to pokaż formułę, będzie łatwiej znaleźć błąd ;)
@@exposepl
Owoc Ilość Cena Wartość Produkt Wartość Owoc Formuła do Wartość: =suma.jeżeli(zaznaczam całość owoc po lewej ; Jagoda; zaznaczam cała wartość) i nic
Agrest
Agrest 6 15,41 zł 92,46 Jagoda 219,91 Banan
Jabłko 9 12,18 zł 396,99 Czereśnia
Banan 9 3,73 zł 109,62 Gruszka
Gruszka 6 9,23 zł 22,38 Jabłko
Kiwi 5 51,13 zł 46,15 Jagoda
Kokos 4 19,20 zł 385,39 Kiwi
Malina 3 24,29 zł 38,40 Kokos
Jagoda 2 59,92 zł 219,91 Malina
Porzeczka1 12,73 zł 258,64 Pomarańcza
Truskawka6 68,82 zł 297,37 Porzeczka
Arbuz 2 95,09 zł 336,10 Truskawka
Czereśnia8 76,48 zł 374,83
@@PanWhitee formuła zwraca jakiś błąd? 0? jakąś konkretną wartość?
@@exposepl generuje mi wynik, ale za jedna sztuke owocu, nie jak u Pana x6
@@PanWhitee a w danych masz więcej niż 1 jagodę? Prześlij plik na lukasz.grochowina@expose.pl to zerknę