< návrat zpět

MS Excel


Téma: Power Query - poslední hodnota rss

Zaslal/a 27.10.2022 23:35

ahojte, opět potřebuji vaši pomoc. Pomalu se učím úplné základy power query a už jsem ze svých souborů dostal hodně. Jednu z posledních věcí, které potřebuju dostat je: v textových souborech jsou na řádcích datumy a já bych potřeboval z posledního datumu dostat hodnotu teploty vlhkosti (nemusí to být maximální/minimální hodnota). Vzorové power query s TXT souborama v příloze. Pomůže někdo?

Díky,
Dalibor

Příloha: zip53735_test2.zip (122kB, staženo 9x)
Zaslat odpověď >

Strana:  « předchozí  1 2 3   další »
#053748
avatar
ahoj elni,

to vypadá dobře, ale nefunguje mi to na celém vzorku 26tis souborů, někde se mu to nepovedlo něco převést na čísla a tak to vyhazuje chybu. Nicméně ty dva poslední sloupce Poslední teplota, Poslední vlhkost vypadají dobře, jen v kódu nemůžu přijít na to, jak jsi to tam dostal????citovat
#053749
elninoslov
No ja som posldné hodnoty riešil v tej funkcii fncGetSoubor:
(Soubor as binary) => let
Zdroj = Csv.Document(Soubor,2,{2,23},null,1250),
#"Nahradená hodnota" = Table.ReplaceValue(Zdroj," "," ",Replacer.ReplaceText,{"Column2"}),
#"Rozdeliť stĺpec podľa oddeľovača" = Table.SplitColumn(#"Nahradená hodnota", "Column2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Teplota nastavená", "T_Eff", "Teplota", "Vlhkost", "Vlhkost nastavená", "Door"}),
#"Zmenený typ pomocou miestneho nastavenia" = Table.TransformColumnTypes(#"Rozdeliť stĺpec podľa oddeľovača", {{"Column1", type datetime}, {"Teplota nastavená", type number}, {"T_Eff", type number}, {"Teplota", type number}, {"Vlhkost", type number}, {"Vlhkost nastavená", type number}}, "en-GB"),
#"Filtrované riadky" = Table.SelectRows(#"Zmenený typ pomocou miestneho nastavenia", each [Door] <> null),
#"Pridané vlastné" = Table.AddColumn(#"Filtrované riadky", "Tolerance teploty MIN", each [Teplota nastavená]-5),
#"Pridané vlastné1" = Table.AddColumn(#"Pridané vlastné", "Tolerance teploty MAX", each [Teplota nastavená]+5),
#"Pridaný podmienený stĺpec" = Table.AddColumn(#"Pridané vlastné1", "Time in spec start", each if [Teplota] >= [Tolerance teploty MIN] then [Column1] else null),
PosledniTeplota = Table.Last(#"Pridaný podmienený stĺpec")[Teplota],
PosledniVlhkost = Table.Last(#"Pridaný podmienený stĺpec")[Vlhkost],

#"Seskupené řádky" = Table.Group(#"Pridaný podmienený stĺpec", {"Teplota nastavená", "Vlhkost nastavená", "Tolerance teploty MIN", "Tolerance teploty MAX"}, {{"Teplota MIN", each List.Min([Teplota]), type number}, {"Teplota MAX", each List.Max([Teplota]), type number}, {"Vlhkost MIN", each List.Min([Vlhkost]), type number}, {"Vlhkost MAX", each List.Max([Vlhkost]), type number}, {"Začátek předehřevu", each List.Min([Column1]), type datetime}, {"Konec předehřevu", each List.Max([Column1]), type datetime}, {"Teplota in spec start", each List.Min([Time in spec start]), type nullable datetime}}),
#"Přidané: Vlastní2" = Table.AddColumn(#"Seskupené řádky", "Total time", each [Konec předehřevu]-[Začátek předehřevu]),
#"Přidané: Vlastní3" = Table.AddColumn(#"Přidané: Vlastní2", "Time in Spec", each [Konec předehřevu]-[Teplota in spec start]),
#"Přidané: Vlastní4" = Table.AddColumn(#"Přidané: Vlastní3", "Náběh teploty", each [Teplota in spec start]-[Začátek předehřevu]),
#"Přidané: Vlastní5" = Table.AddColumn(#"Přidané: Vlastní4", "Poslední teplota", each PosledniTeplota),
#"Přidané: Vlastní6" = Table.AddColumn(#"Přidané: Vlastní5", "Poslední vlhkost", each PosledniVlhkost)

in
#"Přidané: Vlastní6"

No a dotaz test iba vyvoláva funkciu pre každý súbor:
let
Zdroj = Folder.Files(Excel.CurrentWorkbook(){[Name="tblCesta"]}[Content]{0}[Cesta]),
#"Filtrované řádky" = Table.SelectRows(Zdroj, each [Extension] = ".TXT" and not Text.Contains([Name], "Default") and [Attributes]?[Hidden]? <> true),
#"Rozdělit sloupec oddělovačem" = Table.SplitColumn(#"Filtrované řádky", "Name", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"prefix", "Name"}),
#"Rozdělit sloupec oddělovačem1" = Table.SplitColumn(#"Rozdělit sloupec oddělovačem", "Name", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"předehřev", "Name.2", "Nastavený čas", "Batch"}),
#"Nahradená hodnota" = Table.ReplaceValue(#"Rozdělit sloupec oddělovačem1",".TXT","",Replacer.ReplaceText,{"Batch"}),
#"Vyvolat vlastní funkci1" = Table.AddColumn(#"Nahradená hodnota", "Extrakce", each fncGetSoubor([Content])),
#"Odebrané sloupce" = Table.RemoveColumns(#"Vyvolat vlastní funkci1",{"Content", "prefix", "Name.2", "Date accessed", "Date created", "Extension", "Attributes", "Folder Path"}),
#"Rozbalené Extrakce" = Table.ExpandTableColumn(#"Odebrané sloupce", "Extrakce", {"Teplota nastavená", "Vlhkost nastavená", "Tolerance teploty MIN", "Tolerance teploty MAX", "Teplota MIN", "Teplota MAX", "Vlhkost MIN", "Vlhkost MAX", "Začátek předehřevu", "Konec předehřevu", "Total time", "Time in Spec", "Náběh teploty", "Poslední teplota", "Poslední vlhkost"}),
#"Přejmenované sloupce" = Table.RenameColumns(#"Rozbalené Extrakce",{{"Date modified", "Datum modifikace"}}),
#"Stĺpce so zmeneným poradím" = Table.ReorderColumns(#"Přejmenované sloupce",{"Batch", "předehřev", "Nastavený čas", "Datum modifikace", "Začátek předehřevu", "Konec předehřevu", "Teplota nastavená", "Teplota MIN", "Teplota MAX", "Vlhkost nastavená", "Vlhkost MAX", "Vlhkost MIN", "Tolerance teploty MIN", "Tolerance teploty MAX"}),
#"Pridaný podmienený stĺpec" = Table.AddColumn(#"Stĺpce so zmeneným poradím", "Pravda/Nepravda", each DateTime.Date([Datum modifikace]) = DateTime.Date([Konec předehřevu]))
in
#"Pridaný podmienený stĺpec"


EDIT:
Priložte ten súbor, v ktorom nejde previesť tie čísla a hádže chyby.

POZOR! Fórum maže viac ako 1 medzeru za sebou. Takže v tomto riadku
#"Nahradená hodnota" = Table.ReplaceValue(Zdroj," "," ",Replacer.ReplaceText,{"Column2"}),za Zdroj sú v prvých úvodzovkách 2 medzery, v druhých úvodzovkách 1 medzera !!!citovat
#053750
elninoslov
Urobil som pokus. Vaše súbory som si zduplikoval tak, aby bolo načítaných 25000 súborov (38750 je to aj so súbormi Default). Musí sa na to pozrieť odborník na PQ, lebo to trvá strašne dlho. Urobil som na pokus aj makro, posúďte sám:

PowerQuery : 150 s
Makro         :   43 s
Příloha: zip53750_27.10.2022-c.zip (56kB, staženo 4x)
citovat
#053751
avatar
pořád mi to nefunguje, asi mám v databázi i nějaký soubor, který tam dělá neplechu. Zkoušel jsem včera pochopit, jak vytvořit tu funkci v PQ, ale nepřišel jsem na to. Nechci vás kluci obtěžovat, budu pátrat dál, na mě jsou ty funkce v PQ složité, takže pořád nechápu získání té poslední hodnoty z jednoho a druhé sloupce. Ale naučil jsem se díky tobě Elni jak porovnat tisíce souborů z hlediska data modifikace a posledního záznamu v souboru 1 .
Příloha: png53751_snimek-obrazovky-2022-10-29-141721.png (77kB, staženo 10x)
53751_snimek-obrazovky-2022-10-29-141721.png
citovat
#053752
elninoslov
Ak je dotaz ako funkcia, nedá sa krokovať a pozrieť medzivýsledky alebo ladiť. Ja to robím tak, že si ladím na normálnom dotaze, a ten po overení funkčnosti s pevným parametrom prevediem na funkciu. Takže ak si chcete pozrieť ako táto funkcia pracuje so súborom, prerobte si ju opäť na dotaz s pevným parametrom:
let
Soubor = File.Contents("E:\Download\test2\PRE_9-A _02_024_34A221014.TXT"),
Zdroj = Csv.Document(Soubor,2,{2,23},null,1250),
#"Nahradená hodnota" = Table.ReplaceValue(Zdroj," "," ",Replacer.ReplaceText,{"Column2"}),
#"Rozdeliť stĺpec podľa oddeľovača" = Table.SplitColumn(#"Nahradená hodnota", "Column2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Teplota nastavená", "T_Eff", "Teplota", "Vlhkost", "Vlhkost nastavená", "Door"}),
#"Zmenený typ pomocou miestneho nastavenia" = Table.TransformColumnTypes(#"Rozdeliť stĺpec podľa oddeľovača", {{"Column1", type datetime}, {"Teplota nastavená", type number}, {"T_Eff", type number}, {"Teplota", type number}, {"Vlhkost", type number}, {"Vlhkost nastavená", type number}}, "en-GB"),
#"Filtrované riadky" = Table.SelectRows(#"Zmenený typ pomocou miestneho nastavenia", each [Door] <> null),
#"Pridané vlastné" = Table.AddColumn(#"Filtrované riadky", "Tolerance teploty MIN", each [Teplota nastavená]-5),
#"Pridané vlastné1" = Table.AddColumn(#"Pridané vlastné", "Tolerance teploty MAX", each [Teplota nastavená]+5),
#"Pridaný podmienený stĺpec" = Table.AddColumn(#"Pridané vlastné1", "Time in spec start", each if [Teplota] >= [Tolerance teploty MIN] then [Column1] else null),
PosledniTeplota = Table.Last(#"Pridaný podmienený stĺpec")[Teplota],
PosledniVlhkost = Table.Last(#"Pridaný podmienený stĺpec")[Vlhkost],
#"Seskupené řádky" = Table.Group(#"Pridaný podmienený stĺpec", {"Teplota nastavená", "Vlhkost nastavená", "Tolerance teploty MIN", "Tolerance teploty MAX"}, {{"Teplota MIN", each List.Min([Teplota]), type number}, {"Teplota MAX", each List.Max([Teplota]), type number}, {"Vlhkost MIN", each List.Min([Vlhkost]), type number}, {"Vlhkost MAX", each List.Max([Vlhkost]), type number}, {"Začátek předehřevu", each List.Min([Column1]), type datetime}, {"Konec předehřevu", each List.Max([Column1]), type datetime}, {"Teplota in spec start", each List.Min([Time in spec start]), type nullable datetime}}),
#"Přidané: Vlastní2" = Table.AddColumn(#"Seskupené řádky", "Total time", each [Konec předehřevu]-[Začátek předehřevu]),
#"Přidané: Vlastní3" = Table.AddColumn(#"Přidané: Vlastní2", "Time in Spec", each [Konec předehřevu]-[Teplota in spec start]),
#"Přidané: Vlastní4" = Table.AddColumn(#"Přidané: Vlastní3", "Náběh teploty", each [Teplota in spec start]-[Začátek předehřevu]),
#"Přidané: Vlastní5" = Table.AddColumn(#"Přidané: Vlastní4", "Poslední teplota", each PosledniTeplota),
#"Přidané: Vlastní6" = Table.AddColumn(#"Přidané: Vlastní5", "Poslední vlhkost", each PosledniVlhkost)
in
#"Přidané: Vlastní6"

Vymeňte si len názov súboru a môžete krokovať. Tie posledné hodnoty sa uložia do premenných vo funkcii Table.Last. a použijú sa následne po zlúčení riadkov. Je to tam pekne vidieť.

No a čo sa týka tej chyby. Skúsim vymyslieť, ako odhaliť, ktorý súbor robí neplechu. Musíme ho nájsť, aby sme ošetrili aj takú eventualitu. Alebo spustite to makro, a keď makro spadne, tak prečítajte hodnotu Files(i). Tam je názov súboru.

EDIT:
POZOR! Fórum maže viac ako 1 medzeru za sebou. Takže v tomto riadku
#"Nahradená hodnota" = Table.ReplaceValue(Zdroj," "," ",Replacer.ReplaceText,{"Column2"}),za Zdroj sú v prvých úvodzovkách 2 medzery, v druhých úvodzovkách 1 medzera !!!citovat
#053753
avatar
aha, takže jestli to chápu dobře, tak to, co si složitě nakrokuju, tak potom sloučím do funkce, aby to bylo přehlednější, je tak? Já s těma funkcema nemám moc zkušeností, vlastně s celým PQ 3 , ale snažím se. Jdu zkusit pochopit to zpětné převedení funkce na kroky, tam bych to mohl vidět lépe, ty poslední hodnoty by se mi hodily, abych měl kompletní statistický přehled. Dám vědět, jak se mi daří. Díky moc za váš čas.

co si mám představit pod pojmem "s pevným parametrem prevediem na funkciu". S jakým parametrem v tomto případě?

elninoslov(29.10.2022 15:20)#053752
elninoslov
Ak je dotaz ako funkcia, nedá sa krokovať a pozrieť medzivýsledky alebo ladiť. Ja to robím tak, že si ladím na normálnom dotaze, a ten po overení funkčnosti s pevným parametrom prevediem na funkciu. Takže ak si chcete pozrieť ako táto funkcia pracuje so súborom, prerobte si ju opäť na dotaz s pevným parametrom:citovat
#053754
avatar
tak jsem na to chtěl znovu kouknout až jsem se do toho zamotal 3 takže to zkusím od začátku
v TXT souboru řádek
= DEG02.00.10 PRE_9-A _02_024_22A221011
poslední šestičíslí je "Batch" respektive čísla v kodu za posledním podtržítkem a za písmenem (v tomto případě 221011)
024 je "Nastavený čas" a "A" za PRE_9- je "Předehřev".
"Datum modifikace" je poslední uložení souboru.

T_Set T_Eff T_Rec HR_Rec HR_Set Door
T_Set->je nastavená teplota
T_Eff-> z těchto teplot zjistit MIN a MAX teplotu nebo toto zjistit z T_Rec
v případě že z T_Rec, je teplota T_Eff k něčemu?
HR_Set-> Vlhkost nastavená
HR_Rec-> zjistit MIN a MAX
Total time-> rozdíl mezi začátkem ohřevu a koncem ohřevu
Náběh teploty-> kdy se ohřev dostal do tolerance +-5°
Time in spec-> rozdíl mezi začátkem a koncem ohřevu mínus náběh teploty
Poslední teplota a vlhkost->poslední řádky z txt

chápu to takhle správně?citovat
#053755
avatar
Batch je za posledním podtržítkem vím 5citovat
#053756
avatar
ještě to není úplně dodělané ale můžete vyzkoušet
není moc čas
zduplikoval jsem txt soubory a na vzorku cca 8tis souborů i s Default to mě na mém vetránském ntb trvalo cca 1,5 min

nechce mi to nahrát přílohu tak odkaz na edisk
https://www.edisk.cz/stahni/56529/test.rar_8.12MB.html/citovat
#053758
avatar
Kluci, batch je v tomto případě "22A221011", pouze tam někdy chybí A, které signalizuje konec v odpoledních hodinách. Označení předehřevu jsi pochopil správně. Co se týká sloupců tak:

T_set - teplota nastavená
T_Eff - teplota kontrolní čidla (v tomto případě nás nezajímá)
T_Rec - teplota zaznamenaná
HR_Rec - vlhkost zaznamenaná
HR_set - vlhkost nastavená
Door - nás nezajímá

Pokud bych chtěl získat kompletní statistiku tak:

- Potřebuju setřídit podle batcha a podle označení "předehřevu".
- Zjistit MIN a MAX teplotu, to samé pro vlhkost
- Zjistit teplotu a vlhkost z posledního záznamu každého batche
- Celkovou délku každého TXT
- čas, kdy teplota nastoupala 5°C pod nastavenou teplotu, to znamená, že cyklus byl už ve specifikaci
- Zjistit, jestli rok, měsíc, den z posledního řádku TXT souboru sedí s časem modifikace souboru (pravda/nepravda).

Pak by se jednalo o kompletní statistiku 26tis souborů 1 .
Jdu se podívat na to, co poslal Veny. BTW, pořád nechápu, jak tam dostat tu hodnotu poslední teploty a vlhkosti. Nemůžete mi to nechat surové, bez vložených sjednocených funkcí? Třeba bych to lépe pochopil, nejde mi zpět přetransferovat funkci na jednotlivé kroky.

Ale i tak vám moc díky 1

veny napsal/a:

tak jsem na to chtěl znovu kouknout až jsem se do toho zamotal takže to zkusím od začátku
v TXT souboru řádek
= DEG02.00.10 PRE_9-A _02_024_22A221011
poslední šestičíslí je "Batch" respektive čísla v kodu za posledním podtržítkem a za písmenem (v tomto případě 221011)
024 je "Nastavený čas" a "A" za PRE_9- je "Předehřev".
"Datum modifikace" je poslední uložení souboru.

T_Set T_Eff T_Rec HR_Rec HR_Set Door
T_Set->je nastavená teplota
T_Eff-> z těchto teplot zjistit MIN a MAX teplotu nebo toto zjistit z T_Rec
v případě že z T_Rec, je teplota T_Eff k něčemu?
HR_Set-> Vlhkost nastavená
HR_Rec-> zjistit MIN a MAX
Total time-> rozdíl mezi začátkem ohřevu a koncem ohřevu
Náběh teploty-> kdy se ohřev dostal do tolerance +-5°
Time in spec-> rozdíl mezi začátkem a koncem ohřevu mínus náběh teploty
Poslední teplota a vlhkost->poslední řádky z txt

chápu to takhle správně?
citovat

Strana:  « předchozí  1 2 3   další »

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Aktivní diskuse

odpocet a storno tl.

PavDD • 28.3. 8:53

odpocet a storno tl.

Začátečník • 26.3. 14:39

odpocet a storno tl.

PavDD • 26.3. 10:22

odpocet a storno tl.

elninoslov • 26.3. 7:50

odpocet a storno tl.

PavDD • 26.3. 7:26

odpocet a storno tl.

elninoslov • 25.3. 22:34

odpocet a storno tl.

Začátečník • 25.3. 15:09