Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 67265 Skopiuj do schowka Obejście zapętlania zapytania PQ
Autor Wiadomość
fuse
forumowicz


Wersja: Win Office 365
Posty: 12
Wysłany: 16-02-2020, 21:42   Obejście zapętlania zapytania PQ

Dzień dobry,

Mam zestawienie podające składniki w kolejnych procesach produkcji i ich efekt (szczegóły w załączonym pliku). Chcę otrzymać wyliczenie każdego ze składniów w 1 szt. wyrobu. Niestety mój pomysł nie sprawdza się w zestawieniu posiadającym tysiące rekordów, który muszę odświeżać. W zestawieniu, które muszę przetworzyć jest wiele wyrobów, a niektóre składniki powielają się w różnych wyrobach. Różne wyroby produkowane są w różnej ilości procesów - od 1 do 5.

Próbowałem pominąć zapętlenie (scalanie wielokrotnie z bieżacym) przez załadowanie zapytnia do tabeli a następnie scalanie tej tabeli wielokrotnie, ale niewiele to pomaga. Próbowalem też tworzyć drobniejsze zapytania i odświeżać je kolejno manualnie, ale po aktualizacji tabeli początkowej w dalszym ciągu "myśli" tyle, że tracę nadzieję na wynik. :cry:

Używam tylko wbudowanych narzędzi Excela. Na Power Pivot nie mam pomysłu. Wersji Desktop nie posiadam.


PYTANIE 2:
W zapytaniu kilka razy z rzędu scalam po 3 kolumny w jedną, anuluję przestawianie kolumn i rozdzielam ponownie na 3 kolumny. Czy jest prostszy sposób na tę część?

Składniki wyrobu - problem — kopia.xlsx
Zadanie i przykład rozwiązania, które jednak za mocno obciąża Excel.
Pobierz Plik ściągnięto 35 raz(y) 33.28 KB

_________________
Did you excelcise today?
ID posta: 381832 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Pomógł: 248 razy
Posty: 625
Wysłany: 18-02-2020, 13:21   

Sprawdź:
Kod:
let
    Źródło = Excel.CurrentWorkbook(){[Name="TabelkaPoczątkowa6"]}[Content],
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Surowiec", type text}, {"Ilość surowca", type number}, {"Proces", type text}, {"Produkt", type text}, {"Ilość produktu", type number}}),
    #"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each not List.Contains(#"Zmieniono typ"[Produkt], [Surowiec])),
    #"Dodano indeks" = Table.AddIndexColumn(#"Przefiltrowano wiersze", "Indeks", 0, 1),
    #"Zamieniono wartość" = Table.ReplaceValue(#"Dodano indeks",each [Indeks],each if Text.StartsWith([Produkt], "WG") then [Indeks] else null,Replacer.ReplaceValue,{"Indeks"}),
    #"Wypełniono w górę" = Table.FillUp(#"Zamieniono wartość",{"Indeks"}),
    #"Scalone zapytania" = Table.NestedJoin(#"Wypełniono w górę",{"Indeks"},#"Dodano indeks",{"Indeks"},"WG",JoinKind.LeftOuter),
    #"Usunięto kolumny" = Table.RemoveColumns(#"Scalone zapytania",{"Produkt", "Ilość produktu", "Indeks"}),
    #"Rozwinięty element WG" = Table.ExpandTableColumn(#"Usunięto kolumny", "WG", {"Produkt", "Ilość produktu"}, {"Produkt", "Ilość produktu"}),
    #"Dodano kolumnę Ilość surowca" = Table.AddColumn(#"Rozwinięty element WG", "Ilość surowca na 1 szt. WG", each [Ilość surowca]/[Ilość produktu]),
    #"Zmieniono kolejność kolumn" = Table.ReorderColumns(#"Dodano kolumnę Ilość surowca",{"Produkt", "Ilość produktu", "Proces", "Surowiec", "Ilość surowca", "Ilość surowca na 1 szt. WG"})
in
    #"Zmieniono kolejność kolumn"

Czy to odpowiada również na Twoje drugie pytanie? :)

Edit, bo zapomniałem dopisać, a według mnie trzeba to podkreślić: nie dość, że wzorowo przygotowałeś załącznik (wszystko jest dokładnie rozpisane chyba na trzy sposoby), to jeszcze załączyłeś swoją próbę rozwiązania problemu, OGROMNE BRAWA! :danke
ID posta: 381932 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Wersja: Win Office 365
Pomógł: 880 razy
Posty: 3556
Wysłany: 19-02-2020, 11:23   

fuse, ode mnie również Brawa za szczegółowo opisany załącznik :clap
DwaNiedźwiadki, ładny kodzik :tak
Jedyne czego można się obawiać to jakieś nazwy zamiast WG (rozumiem, że to skrót od Wyrób Gotowy?). Ale i z tego dałoby się wybrnąć ustalając unikaty z kolumny "Surowiec" oraz z Kolumny "Proces" i wyszukując różnice w tych dwóch listach. Tym różnicom można następnie przypisać odpowiednie kody WG (czyli WG1, WG2... itd) a następnie dodać warunkową kolumnę do tabeli głownej zamieniającej te nazwy na odpowiednie WG-nazwy. Dalej już to samo co u Ciebie w kodzie (z tym że rozpatrujemy tą nowo utworzoną kolumnę zamiast oryginału)
_________________
Zlecenia, konsultacje, doradztwo i szkolenia z Power Query, Power BI i Excela - Raporty, Analizy, Projekty
Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 381979 Skopiuj do schowka
 
 
fuse
forumowicz


Wersja: Win Office 365
Posty: 12
Wysłany: 20-02-2020, 20:49   

DwaNiedźwiedzie,

Dziś udało mi się przetestować zapytanie na danych docelowych. Blisko 10 minut trwało przetworzenie ~4600 rekordów. Musiałem dostosować filtrowanie do moich danych, po czym znów przetwarzał dane podobną ilość czasu. Obawiam się, że przy tabeli o 50tys wierszy będę mógł iść na przerwę. Tego już nie obejdę, więc Dziękuję!

NAWET BARDZO!, bo z jaką prędkością by nie dizałało, to zapytanie się sprawdza i jest wyjątkowo krótkie - ten poziom zzawansowania spowodował, że ja z moimi "umiejętnościami" poczułem się drobny jak przecinek w piątym zdaniu siódmej ksiegi "Pana Tadeusza". W kodzie z reguły pokuszam się o minimalne modyfikacje (niestety nie mam w tym doświadczenia i raczej w tym raczkuję), a to co ujrzałem było powiewem świeżości... Takie drugie pierwsze wrażenie! :)

Nie dość, że otrzymałem rozwiazanie, to ujrzałem inne oblicze narzędzia. :mrgreen: Może uda mi się zgłębić te tajniki przy rozwiązywaniu innego problemu.

Tę sprawę mogę uznać za rozwiązaną. :-)
_________________
Did you excelcise today?
ID posta: 382121 Skopiuj do schowka
 
 
fuse
forumowicz


Wersja: Win Office 365
Posty: 12
Wysłany: 20-02-2020, 20:57   

Bill Szysz, tak "WG" było przykładowe, ale ustalenie tych kodów zamyka się max. w 3-4 prostych warunkach, więc nie chciałem komplikować. Dziękuję za zainteresowanie i ponowną pomoc! :-)
_________________
Did you excelcise today?
ID posta: 382123 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Pomógł: 248 razy
Posty: 625
Wysłany: 20-02-2020, 22:57   

No to lekcja druga: optymalizacja :) Kod działa wolno, ponieważ na kroku filtrowania zbędnych wierszy z produktami wielokrotnie odnosi się do pełnej kolumny w tabeli z poprzedniego kroku i o ile na małej próbce nie było to problemem, tak szybko staje się odczuwalne przy większej ilości danych. W poniższej kwerendzie zrobiłem listę w osobnym kroku i dodatkowo wrzuciłem ją do pamięci - daj znać, jak teraz hula :) Możesz też spróbować zbuforować tabelę w pierwszym kroku, choć tu już pewnie nie będzie aż tak spektakularnej różnicy.
Kod:
let
    Źródło = Excel.CurrentWorkbook(){[Name="TabelkaPoczątkowa6"]}[Content],
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Surowiec", type text}, {"Ilość surowca", type number}, {"Proces", type text}, {"Produkt", type text}, {"Ilość produktu", type number}}),
    lista = List.Buffer(List.Distinct(#"Zmieniono typ"[Produkt])),
    #"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each not List.Contains(lista, [Surowiec])),
    #"Dodano indeks" = Table.AddIndexColumn(#"Przefiltrowano wiersze", "Indeks", 0, 1),
    #"Zamieniono wartość" = Table.ReplaceValue(#"Dodano indeks",each [Indeks],each if Text.StartsWith([Produkt], "WG") then [Indeks] else null,Replacer.ReplaceValue,{"Indeks"}),
    #"Wypełniono w górę" = Table.FillUp(#"Zamieniono wartość",{"Indeks"}),
    #"Scalone zapytania" = Table.NestedJoin(#"Wypełniono w górę",{"Indeks"},#"Dodano indeks",{"Indeks"},"WG",JoinKind.LeftOuter),
    #"Usunięto kolumny" = Table.RemoveColumns(#"Scalone zapytania",{"Produkt", "Ilość produktu", "Indeks"}),
    #"Rozwinięty element WG" = Table.ExpandTableColumn(#"Usunięto kolumny", "WG", {"Produkt", "Ilość produktu"}, {"Produkt", "Ilość produktu"}),
    #"Dodano kolumnę Ilość surowca" = Table.AddColumn(#"Rozwinięty element WG", "Ilość surowca na 1 szt. WG", each [Ilość surowca]/[Ilość produktu]),
    #"Zmieniono kolejność kolumn" = Table.ReorderColumns(#"Dodano kolumnę Ilość surowca",{"Produkt", "Ilość produktu", "Proces", "Surowiec", "Ilość surowca", "Ilość surowca na 1 szt. WG"})
in
    #"Zmieniono kolejność kolumn"
ID posta: 382126 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Wersja: Win Office 365
Pomógł: 880 razy
Posty: 3556
Wysłany: 20-02-2020, 23:03   

fuse, ten czas wykonania jest nie do przyjęcia... powinno baaaardzo przyspieszyć gdy użyjemy buforowania ( pewnie dla tych 4600 rekordów będzie to kilka sekund).
Poniżej poprawiony kod Niedzwiadków - dodałem buforowanie listy z której korzystamy w każdym (sprawdzanym przy filtrowaniu) wierszu.
Kod:
let
    Źródło = Excel.CurrentWorkbook(){[Name="TabelkaPoczątkowa6"]}[Content],
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Surowiec", type text}, {"Ilość surowca", type number}, {"Proces", type text}, {"Produkt", type text}, {"Ilość produktu", type number}}),
    BufferedList = List.Buffer(List.Distinct(#"Zmieniono typ"[Produkt])),
    #"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each not List.Contains(BufferedList, [Surowiec])),
    #"Dodano indeks" = Table.AddIndexColumn(#"Przefiltrowano wiersze", "Indeks", 0, 1),
    #"Zamieniono wartość" = Table.ReplaceValue(#"Dodano indeks",each [Indeks],each if Text.StartsWith([Produkt], "WG") then [Indeks] else null,Replacer.ReplaceValue,{"Indeks"}),
    #"Wypełniono w górę" = Table.FillUp(#"Zamieniono wartość",{"Indeks"}),
    #"Scalone zapytania" = Table.NestedJoin(#"Wypełniono w górę",{"Indeks"},#"Dodano indeks",{"Indeks"},"WG",JoinKind.LeftOuter),
    #"Usunięto kolumny" = Table.RemoveColumns(#"Scalone zapytania",{"Produkt", "Ilość produktu", "Indeks"}),
    #"Rozwinięty element WG" = Table.ExpandTableColumn(#"Usunięto kolumny", "WG", {"Produkt", "Ilość produktu"}, {"Produkt", "Ilość produktu"}),
    #"Dodano kolumnę Ilość surowca" = Table.AddColumn(#"Rozwinięty element WG", "Ilość surowca na 1 szt. WG", each [Ilość surowca]/[Ilość produktu]),
    #"Zmieniono kolejność kolumn" = Table.ReorderColumns(#"Dodano kolumnę Ilość surowca",{"Produkt", "Ilość produktu", "Proces", "Surowiec", "Ilość surowca", "Ilość surowca na 1 szt. WG"})
in
    #"Zmieniono kolejność kolumn"


A tu mój kod - ciut inna koncepcja (nie wiem czy szybszy ale na oko odrobinę szybszy powinien być)
Kod:
let
    Źródło = Excel.CurrentWorkbook(){[Name="TabelkaPoczątkowa6"]}[Content],
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Surowiec", type text}, {"Ilość surowca", type number}, {"Proces", type text}, {"Produkt", type text}, {"Ilość produktu", type number}}),
    BufferedList = List.Buffer(List.Distinct(#"Zmieniono typ"[Produkt])),
    #"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each not List.Contains(BufferedList, [Surowiec])),
    #"Added Conditional Column" = Table.AddColumn(#"Przefiltrowano wiersze", "WG", each if Text.StartsWith([Produkt], "WG") then [Produkt] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Ilość", each if [WG] <> null then [Ilość produktu] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"WG", "Ilość"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Filled Up",{"WG", "Ilość", "Proces", "Surowiec", "Ilość surowca"}),
    #"Inserted Division" = Table.AddColumn(#"Removed Other Columns", "Ilość na 1 WG", each [Ilość surowca] / [Ilość], type number)
in
    #"Inserted Division"

Zmień sobie nazwy kolumn bo już mi się nie chciało ;-)
Wg moich przypuszczeń powinno być (przy obu kodach) o niebo szybciej

EDIT: Nie widziałem Twojego posta DwaPiwka ;-) ...ale teraz widzę, że spostrzeżenia mieliśmy te same :-) )
_________________
Zlecenia, konsultacje, doradztwo i szkolenia z Power Query, Power BI i Excela - Raporty, Analizy, Projekty
Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 382127 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Pomógł: 248 razy
Posty: 625
Wysłany: 20-02-2020, 23:29   

Moja poprawiona kwerenda przy 23,5k wierszy na wejściu (powielone 20 z oryginału z nadanymi unikatowymi WGxxx) miała u mnie średni czas wykonywania ~10,2s (z buforowaniem źródła 8,6s), ciut inna od Billa ~6,5s, ale ja przebijam jeszcze ciut inniejszą ze starym, dobrym joinem, która osiąga 5,1s :)
Kod:
let
    Źródło = Table.Buffer(Excel.CurrentWorkbook(){[Name="TabelkaPoczątkowa6"]}[Content]),
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Surowiec", type text}, {"Ilość surowca", type number}, {"Proces", type text}, {"Produkt", type text}, {"Ilość produktu", type number}}),
    #"Scalone zapytania1" = Table.NestedJoin(#"Zmieniono typ",{"Surowiec"},#"Zmieniono typ",{"Produkt"},"Nowa kolumna",JoinKind.LeftAnti),
    #"Usunięto kolumny1" = Table.RemoveColumns(#"Scalone zapytania1",{"Nowa kolumna"}),
    #"Dodano indeks" = Table.AddIndexColumn(#"Usunięto kolumny1", "Indeks", 0, 1),
    #"Zamieniono wartość" = Table.ReplaceValue(#"Dodano indeks",each [Indeks],each if Text.StartsWith([Produkt], "WG") then [Indeks] else null,Replacer.ReplaceValue,{"Indeks"}),
    #"Wypełniono w górę" = Table.FillUp(#"Zamieniono wartość",{"Indeks"}),
    #"Scalone zapytania2" = Table.NestedJoin(#"Wypełniono w górę",{"Indeks"},#"Dodano indeks",{"Indeks"},"WG",JoinKind.LeftOuter),
    #"Usunięto kolumny2" = Table.RemoveColumns(#"Scalone zapytania2",{"Produkt", "Ilość produktu", "Indeks"}),
    #"Rozwinięty element WG" = Table.ExpandTableColumn(#"Usunięto kolumny2", "WG", {"Produkt", "Ilość produktu"}, {"Produkt", "Ilość produktu"}),
    #"Dodano kolumnę Ilość surowca" = Table.AddColumn(#"Rozwinięty element WG", "Ilość surowca na 1 szt. WG", each [Ilość surowca]/[Ilość produktu]),
    #"Zmieniono kolejność kolumn" = Table.ReorderColumns(#"Dodano kolumnę Ilość surowca",{"Produkt", "Ilość produktu", "Proces", "Surowiec", "Ilość surowca", "Ilość surowca na 1 szt. WG"})
in
    #"Zmieniono kolejność kolumn"
ID posta: 382130 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Wersja: Win Office 365
Pomógł: 880 razy
Posty: 3556
Wysłany: 21-02-2020, 11:10   

Niedźwiadki, no ładnie :mrgreen:
To ja dla odmiany cos tam pokombinowałem (z buforowana listą) i puściłem zapytanko na ciut ponad 126 000 rekordów (18000 różnych WG i 70 różnych surowców typu białko, cukier, aromat - po prostu dodałem pare numerków do nich).
Pomierzyłem (tylko stoperem w komórce bo nie tysięczne czy nawet dziesiętne części sekundy chodzi) czasy dla Twojego (ostatniego z joinem) i mojego (tego poniżej) zapytania - trzy próby dla każdego z nich i wyliczona średnia.
Twoje (podkreślam, że u mnie na 32 bitowym excelku) - średnia 32,06
Moje - średnia 25,56
A poniżej kodzik nowy
Kod:
let
    Źródło = Table.Buffer(Excel.CurrentWorkbook(){[Name="TabelkaPoczątkowa6"]}[Content]),
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Surowiec", type text}, {"Ilość surowca", type number}, {"Proces", type text}, {"Produkt", type text}, {"Ilość produktu", type number}}),
    BufferedList = List.Buffer(List.Difference(List.Distinct(#"Zmieniono typ"[Surowiec]),List.Distinct(#"Zmieniono typ"[Produkt]))),
    #"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each List.Contains(BufferedList, [Surowiec])),
    #"Added Conditional Column" = Table.AddColumn(#"Przefiltrowano wiersze", "WG", each if Text.StartsWith([Produkt], "WG") then [Produkt] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Ilość", each if [WG] <> null then [Ilość produktu] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"WG", "Ilość"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Filled Up",{"WG", "Ilość", "Proces", "Surowiec", "Ilość surowca"}),
    #"Inserted Division" = Table.AddColumn(#"Removed Other Columns", "Ilość na 1 WG", each [Ilość surowca] / [Ilość], type number)
in
    #"Inserted Division"

Na koniec - nie mam pojęcia które z naszych zapytań będzie szybsze na prawdziwych danych bo jak widac optymalizacja "niejedno ma imię" :mrgreen:
_________________
Zlecenia, konsultacje, doradztwo i szkolenia z Power Query, Power BI i Excela - Raporty, Analizy, Projekty
Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 382145 Skopiuj do schowka
 
 
fuse
forumowicz


Wersja: Win Office 365
Posty: 12
Wysłany: 21-02-2020, 19:09   

Bill Szysz, tak, długie działanie to było, ale bez świadomości, że można krócej nie mogę kręcić nosem. I tak jestem zadowolony :-)

Teraz hula i nie szumi już laptop przy uruchamianiu zapytania. Zestawienie mam w 3-4 sek. :mrgreen: Czas na dokładniejsze prześledzenie i zrozumienie kodu.

Podekscytowany jestem od wczoraj uzyskanym efektem i przede wszystkim sposobem! Dziękuję Wam bardzo! DwaNiedźwiedzie, :danke Bill Szysz, :danke
_________________
Did you excelcise today?
ID posta: 382170 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Pomógł: 248 razy
Posty: 625
Wysłany: 22-02-2020, 11:27   

W sumie dopiero teraz przyjrzałem się "ze zrozumieniem" kwerendzie Billa i zastanawiam się, czemu wcześniej nie dostrzegłem tak prostej zależności :D Przecież kombinowanie z indeksem i "joinami" zupełnie nie ma tutaj sensu, gdyż dużo prościej od razu przepisać nazwę produktu i ilość w odpowiednich miejscach i wypełnić je w górę. Jednak w wyścigu nie daję za wygraną: ostatnia kwerenda Billa ma (przy moich poprzednich warunkach) średni wynik 4,8s, a poniższa 2s (*) :D
Kod:
let
    Źródło = Excel.CurrentWorkbook(){[Name="TabelkaPoczątkowa6"]}[Content],
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Surowiec", type text}, {"Ilość surowca", type number}, {"Proces", type text}, {"Produkt", type text}, {"Ilość produktu", type number}}),
    lista = List.Buffer(List.Distinct(#"Zmieniono typ"[Produkt])),
    #"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each not List.Contains(lista, [Surowiec])),
    #"Dodano kolumnę WG" = Table.AddColumn(#"Przefiltrowano wiersze", "WG", each if Text.StartsWith([Produkt], "WG") then [Produkt = [Produkt], #"Ilość produktu" = [Ilość produktu]] else null),
    #"Wypełniono w górę" = Table.FillUp(#"Dodano kolumnę WG",{"WG"}),
    #"Usunięto kolumny" = Table.RemoveColumns(#"Wypełniono w górę",{"Produkt", "Ilość produktu"}),
    #"Rozwinięty element WG" = Table.ExpandRecordColumn(#"Usunięto kolumny", "WG", {"Produkt", "Ilość produktu"}, {"Produkt", "Ilość produktu"}),
    #"Dodano kolumnę Ilość na WG" = Table.AddColumn(#"Rozwinięty element WG", "Ilość na 1 WG", each [Ilość surowca] / [Ilość produktu]),
    #"Zmieniono kolejność kolumn" = Table.ReorderColumns(#"Dodano kolumnę Ilość na WG",{"Produkt", "Ilość produktu", "Proces", "Surowiec", "Ilość surowca", "Ilość na 1 WG"})
in
    #"Zmieniono kolejność kolumn"

Bill dwukrotnie sprawdza, czy dany wiersz jest produktem końcowym, z którego należy pobrać nazwę i ilość. Ja się ostatnio głowiłem nad zadaniem, w którym taki warunek musiałbym sprawdzać wielokrotnie i doszedłem do wniosku, że znacznie szybciej i wygodniej jest sprawdzić to raz i przy okazji skorzystać z genialnej "obiektowości" danych, jaką oferuje PQ, czyli potrzebne informacje dopisać w formie rekordu z kilkoma polami, a później go rozwinąć do odrębnych kolumn.

* - edit: wynik prawdopodobnie został osiągnięty przy innej konfiguracji połączenia, szczegóły poniżej w PS do mojego kolejnego postu
Ostatnio zmieniony przez DwaNiedźwiedzie 25-02-2020, 11:00, w całości zmieniany 1 raz  
ID posta: 382196 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Wersja: Win Office 365
Pomógł: 880 razy
Posty: 3556
Wysłany: 23-02-2020, 12:55   

DwaNiedźwiedzie, czy mógłbys podać na jakim sprzęcie pracujesz i jaką masz wersje excelka (64 czy 32bitowy). Pytam bo na moim sprzęcie nijak nie mogłem uzyskać przewagi Twojego ostatniego rozwiązania (nota bene fajnego :D).
Co do techniki wykorzystania rekordów - często ją stosuję (choćby przy List.Generate ale nie tylko). Często robię to by przechować jakąś obliczoną wartość czy inny obiekt i z niej/niego korzystać w innych polach tego samego budowanego rekordu. Mam na myśli coś takiego.
Kod:
[Wsad = JakaśObliczonaWartośćNpNaPodstawieTabeliCzyListy, Wynik1 = Wsad/[Pole z wiersza], Wynik2 = Wynik1*CośTam]

Powyższe jest baaaaardzo uproszczonym przykładem (gdy potrzebuje jednocześnie Wynik1 i Wynik2) :-)
_________________
Zlecenia, konsultacje, doradztwo i szkolenia z Power Query, Power BI i Excela - Raporty, Analizy, Projekty
Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 382260 Skopiuj do schowka
 
 
fuse
forumowicz


Wersja: Win Office 365
Posty: 12
Wysłany: 24-02-2020, 22:38   

Przejrzałem kroki i zapromonowane rozwiązania sprawdzą się, gdy tabela źródłowa jest ułożona (po półproduktach będących składnikami wyrobu 1 jest ostatni etap - produkcja wyrobu 1, potem półprodukty wyrobu 2 i wyrób 2, półprodukty wyrobu 3 i wyrób 3...). Niestety nie zawsze będzie u mnie tak pięknie :cry: Co można zrobić, jeśli nie będzie zachowane takie ułożenie? Czy muszę zapętlać, jak robiłem to do tej pory, czy da się to jakoś obejść? Użyte "Wypełnij w górę" nie da poprawnego efektu. Przedstawiłem to w arkuszu ZADANIE_V2.


Składniki wyrobu - problem_rozwiązanie_v2 &#8212; kopia.xlsx
Arkusz "Zadanie_v2", gdy etapy nie są ułożone chronologicznie.
Pobierz Plik ściągnięto 16 raz(y) 79.95 KB

_________________
Did you excelcise today?
ID posta: 382378 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Pomógł: 248 razy
Posty: 625
Wysłany: 25-02-2020, 10:38   

Sprawdź coś takiego - kwerenda powinna działać również dla zadania v3 (bo pewnie do tego zmierzasz :), ale chyba troszkę namieszałeś z prezentacją oczekiwanych wyników, bo na moje oko nie są prawidłowe.
Kod:
let
     Źródło = Excel.CurrentWorkbook(){[Name="TabelkaPoczątkowa_v2"]}[Content],
     #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Surowiec", type text}, {"Ilość surowca", type number}, {"Proces", type text}, {"Produkt", type text}, {"Ilość produktu", type number}}),
     #"Przefiltrowano produkty" = Table.SelectRows(#"Zmieniono typ", each Text.StartsWith([Produkt], "WG")),
     #"Usunięto inne kolumny" = Table.SelectColumns(#"Przefiltrowano produkty",{"Produkt", "Ilość produktu"}),
     #"Usunięto duplikaty1" = Table.Distinct(#"Usunięto inne kolumny", {"Produkt"}),
     surowce = List.Buffer(List.Distinct(#"Zmieniono typ"[Produkt])),
     skład = Table.SelectRows(#"Zmieniono typ", each not List.Contains(surowce, _[Surowiec])),
     #"Usunięto duplikaty" = Table.Distinct(skład),
     #"Przefiltrowano wiersze" = Table.Buffer(Table.SelectRows(#"Zmieniono typ", each List.Contains(surowce, [Surowiec]))),
     #"Dodano kolumnę składników" = Table.AddColumn(#"Usunięto duplikaty1", "składniki", (rek) => List.Generate(() => rek[Produkt], each List.Contains(surowce, _), (rek2) =>  try Table.SelectRows(#"Przefiltrowano wiersze", each [Produkt] = rek2)[Surowiec]{0} otherwise null)),
     #"Rozwinięty element kroki" = Table.ExpandListColumn(#"Dodano kolumnę składników", "składniki"),
     #"Scalone zapytania" = Table.NestedJoin(#"Rozwinięty element kroki",{"składniki"},#"Usunięto duplikaty",{"Produkt"},"dopisanie",JoinKind.LeftOuter),
     #"Usunięto kolumny" = Table.RemoveColumns(#"Scalone zapytania",{"składniki"}),
     #"Rozwinięty element Nowa kolumna" = Table.ExpandTableColumn(#"Usunięto kolumny", "dopisanie", {"Surowiec", "Ilość surowca", "Proces"}),
     #"Posortowano wiersze" = Table.Sort(#"Rozwinięty element Nowa kolumna",{{"Produkt", Order.Ascending}, {"Proces", Order.Ascending}, {"Surowiec", Order.Ascending}}),
     #"Zmieniono kolejność kolumn" = Table.ReorderColumns(#"Posortowano wiersze",{"Produkt", "Ilość produktu", "Proces", "Surowiec", "Ilość surowca"}),
     #"Dodano kolumnę ilość WG" = Table.AddColumn(#"Zmieniono kolejność kolumn", "Ilość surowca na sztukę wyrobu", each [Ilość surowca]/[Ilość produktu])
in
     #"Dodano kolumnę ilość WG"

PS: Bill, zerknąłem wczoraj na plik testowy i wygląda na to, że wyniki faktycznie mogą być niemiarodajne - tę najszybszą kwerendę wrzuciłem do istniejącego w pliku połączenia (nadpisałem jego kod), które było utworzone przez fuse i w przeciwieństwie do pozostałych miało wyłączone dodawanie do modelu. Jak będę miał natchnienie, to zrobię nowe testy w czystym skoroszycie, a póki co biję się w klatę i złażę z podium :)
ID posta: 382402 Skopiuj do schowka
 
 
fuse
forumowicz


Wersja: Win Office 365
Posty: 12
Wysłany: 25-02-2020, 23:02   

DwaNiedźwiedzie,
wczoraj już padaliśmy z lapkiem - szykowałem rozszerzenie problemu o sytuację, gdy jeden produkt nie jest składnikiem tylko jednego wyrobu, ale nie podjąłem się jego poruszenia. I tak mi dużo pomogliście, dzięki temu coś już ogarnę w prostszych zestawieniach :-) Muszę jeszcze to poćwiczyć i zrozumieć.

Ale jeślu już sprawa wypłynęła, to wyliczenie zużycia ma być proporcjonalnie wedle podzialu w którymś z etapów. Prostym domowym przykładem - 1 litr ugotowanego mleka można podzielić po połowie na budyń (w 3 miskach) i owsiankę (w 2 miskach). W tym przypadku ilość mleka na 1 miskę budyniu jest 0,5/3 = 0,167 l, a na miskę owsianki - 0,25 l.

PS. czy używane w ostanim zapytaniu "rek", "rek2" to jakieś funkcje, zmienne czy coś w ogóle innego? Nie ogarniam tego kroku, a do tego jeszcze jakieś strzałki "=>" tam są. Nie spotkałem się nigdy z czymś takim i nie łapię. Nie chcę też na ślepo kopiować kodu, a go zrozumieć :-( O buforowaniu tableli znalazłem tylko informacje, żeby próboać i albo przyspieszy albo wydłuży i też nie wiem, kiedy stosować...

Składniki wyrobu - problem_rozwiązanie_v3.xlsx
Opis, gdy 1 półprodukt staje się składnikiem różnych wyrobów (arkusz ZADANIE_V3)
Pobierz Plik ściągnięto 18 raz(y) 92.14 KB

_________________
Did you excelcise today?
ID posta: 382461 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe