Příspěvky uživatele
< návrat zpět
Co to dělá
GetDataFromFiles:
let
// Načteme adresář a vybereme textové soubory
Zdroj = Folder.Files(Excel.CurrentWorkbook(){[Name="tblDirPath"]}[Content]{0}[Directory path]),
#"Filtrované riadky" = Table.SelectRows(Zdroj, each [Extension]=".TXT"),
// Ve sloupci "Content" je binárně obsah načtených souborů
// Zbytek nepotřebujeme (pokud nechceme ponechat datum změny...)
#"Odebrané ostatní sloupce" = Table.SelectColumns(#"Filtrované riadky",{"Content"}),
// Obsah jednotlivých buněk sloupce "Content" transformujeme funkcí fncGetDataFromFile
// pamatujeme, že v každé buňce je binární obsah jednoho souboru
// tento kód vygenerovalo PQ. Proti běžným postupům využívá volitelného parametru, který umožňuje zadat společnou transformaci
// pro všechny sloupce tabulky neuvedené v předchozím seznamu (ten je tady prázdný)
#"Transformovaný sloupec" = Table.TransformColumns(#"Odebrané ostatní sloupce",{},fncGetDataFromFile),
// Pokud jsou v buňkách sloupce tabulky, je sloupci přiřazen typ tabulka ale s konkrétním typem "any",
// tj. s původními typy sloupců se tady nepočítá a typ bude nutné obnovit.
// Lze použít postup na konci funkce fncGetDataFromFile
// Někde jsem kdysi zahlédl zajímavou ideu jak na to jinak
// Funkce Table.TransformColumns umožňuje zadat typ sloupce po transformaci
// Tady transformační funkce vrací původní obsah buňky
// a deklaruje typ sloupce podle tabulky v prvním řádku sloupce.
#"Obnovíme typ" =
Table.TransformColumns(#"Transformovaný sloupec",
{"Content",
each {_}{0},
Value.Type(Table.Column(#"Transformovaný sloupec", "Content"){0})
}),
// Expandujeme sloupec "Content" s tabulkami.
// normálně to generuje kód
// = Table.ExpandTableColumn(#"Obnovíme typ", "Content", {"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14", "D15", "D16", "D17"}, {"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14", "D15", "D16", "D17"})
// nevýhodou je, že pokud se přidá další sloupec, tak se nerozbalí
// Proto je v sešitu tabulka sloupce, ve které je uveden krátký název, název pro report a typ.
// pokud se přidá nová sloupec, stačí ho doplnit v listu excelu.
#"Rozbalené Content" = Table.ExpandTableColumn(#"Obnovíme typ", "Content", Sloupce[Zkratka]),
// Stejnou tabulku použijeme pro přejmenování
#"Přejmenované sloupce" = Table.RenameColumns(#"Rozbalené Content", List.Transform(Table.ToRows(Sloupce), each {_{0}, _{1}} ), MissingField.UseNull)
// Poslední dva příkazy bylo možné zapsat:
//= Table.ExpandTableColumn(#"Obnovíme typ", "Content", Sloupce[Zkratka], Sloupce[Jméno])
in
#"Přejmenované sloupce"
Jak se to píše:
Klikání neumožní vytvářet flexibilní kód. Na druhé straně psát dlouhé konstrukce je taky otrava => základ naklikám a pak ručně doladím.
Na popis funkce potřebuji víc času.
jaks.dalibor napsal/a:
1. číslo zařízení (je to první dvojčíslí z batch - příklad .... 37211028, tak číslo zařízení je 37
Velmi jednoduché. Stačí ve finální tabulce přidat sloupec. Dá se i vzorcem.
jaks.dalibor napsal/a:
2. Datum - ten se vypočítá z batch a je to poslední šestičíslí ve tvaru YYMMDD - příklad 31211028 je 28.10.2021
Velmi jednoduché. Stačí ve finální tabulce přidat sloupec. Dá se i vzorcem.
jaks.dalibor napsal/a:
3. šlo by vložit sloupec, kdy byl soubor změněn? Porovnal bych to pak s datumem a do dalšího sloupce bych vložil jen nějaký znak, pokud by datum z bodu 2. bylo rozdílně od datumu, kdy byl soubor změněn
Stačí v načtené tabulce ponechat sloupec "Date modified"
#"Odebrané ostatní sloupce" = Table.SelectColumns(#"Filtrované riadky",{"Content", "Date modified"}),
#"Transformovaný sloupec" = Table.TransformColumns(#"Odebrané ostatní sloupce",{"Content", fncGetDataFromFile}),
Je třeba také upravit #"Transformovaný sloupec". V kódu byla aututomaticky vygenerovaná, méně známá syntaxe funkce Table.TransformColumns.
jaks.dalibor napsal/a:
4. třešnička na dortu by byla, kdyby se z těch batch čísel staly hypertextové odkazy, které by odkazovaly na ty TXT soubory, takže když se ti nebude něco zdát, tak na ten batch klikneš a otevře se ti TXT soubor.
Jedině VBA.
jaks.dalibor napsal/a:
Lubo, ten tvůj soubor mi nějak nefunguje, občas mi to vyhazuje errory, nebo nenačte celý řádek, ikdyž tam data jsou, nevím proč.
Já taky nevím proč to hlásí chyby. Je zřejmé, že soubory neodpovídají předpokladům.
Tak aspoň pár tipů, na co se můžeš podívat:
Při načítání souboru nepočítám s uvozovkami - QuoteStyle.None.
Pozice sloupců jsou nastaven podle zaslaných příkladů.
V kroku "#"Vyčištěný text" se ošetřují mezery mezi znaménkem - a číslicí jen ve sloupci "PR03(kPa)". Jinde jsem si toho nevšiml.
Přidal jsem to do souboru 51387_51380_data-pq.xlsx.
Doplnil jsem pár drobností, vč. přidání průměru, maxima, ...
Snad to je pochopitelné.
Jinak, jak zmínil elninoslov, máš v definici počítaných hodnot docela zmatek.
Původní dotazy jsem nechal s verzí (2).
Asi všechno hned nepochopíš, ale podle vzoru by snad úpravy počítaných hodnot nemusely být problém.
zdravím pánové,
dovolil jsem si navrhnout některé úpravy, které mohou zjednodušit případné úpravy kódu.
Poznámky:
a{x}? pokud prvek seznamu neexistuje, zamezí chybě a vrátí null
a[x]? obdoba pro record
Otazníky jsou tedy důležité.
Některé dlouhé seznamy je vhodné udržovat v tabulce excelu a v kóde se na ně odvolávat. Je snažší pro údržbu.
Ze stejného důvodu jsem pro počítané proměnné deklaroval funkce.
V Table.Group jsou ukázky jak počítat průměry, minima,
ten první řádek je už pro pokročilejší. Je nutné vědět, že Table.Group do vnořených výpočtů posílá tabulku se všemi řádky skupiny.
Způsob načtení souborů je podle mých zkušeností o něco rychlejší, než ten automaticky generovaný (a i kód je kratší)
Načtení souborů může tedy vypadat
// GetDataFromFiles
let
Zdroj = Folder.Files(Excel.CurrentWorkbook(){[Name="tblDirPath"]}[Content]{0}[Directory path]),
#"Filtrované riadky" = Table.SelectRows(Zdroj, each [Extension]=".TXT"),
#"Odebrané ostatní sloupce" = Table.SelectColumns(#"Filtrované riadky",{"Content"}),
#"Transformovaný sloupec" = Table.TransformColumns(#"Odebrané ostatní sloupce",{},fncGetDataFromFile),
#"Rozbalené Content" = Table.ExpandTableColumn(#"Transformovaný sloupec", "Content", {"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14"}, {"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14"}),
#"Přejmenované sloupce" = Table.RenameColumns(#"Rozbalené Content",List.Zip({
{"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14"},
{"Batch", "Recipe", "Temperature", "Initial vacuum", "Conditioning duration", "Humidity", "Gas inlet duration", "EO weight", "Sterilization pressure", "Dwell", "Final vacuum", "Number of washings", "Washing vacuum", "Initial vacuum 2", "Initial vacuum duration", "Rh at the end of conditioning", "Conditioning duration 2", "Pressure at the end of conditioning", "Temperature at the end of conditioning", "Pressure increment", "Pressure after EO inject", "EO inject duration", "Dwell duration", "Pressure at the end of DWELL", "Pressure leak", "Final vacuum 2", "Number of washes"}
})),
#"Zmenený typ" = Table.TransformColumnTypes(#"Přejmenované sloupce",{{"Batch", type text}, {"Recipe", type number}, {"Temperature", type number}, {"Initial vacuum", type number}, {"Conditioning duration", type number}, {"Humidity", type number}, {"Gas inlet duration", type number}, {"EO weight", type number}, {"Sterilization pressure", type number}, {"Dwell", type number}, {"Final vacuum", type number}, {"Number of washings", type number}, {"Washing vacuum", type number}, {"Initial vacuum 2", type number}, {"Rh at the end of conditioning", type number}, {"Pressure at the end of conditioning", type number}, {"Temperature at the end of conditioning", type number}, {"Pressure increment", type number}, {"Pressure after EO inject", type number}, {"Pressure at the end of DWELL", type number}, {"Pressure leak", type number}, {"Final vacuum 2", type number}, {"Number of washes", Int64.Type}, {"Initial vacuum duration", type time}, {"Conditioning duration 2", type time}, {"EO inject duration", type time}, {"Dwell duration", type time}})
in
#"Zmenený typ"
a procedura
// fncGetDataFromFile
(FileContent as any) =>
let
// Načteme po řádcich a rozdělíme na části
File = Lines.FromBinary(FileContent, QuoteStyle.None, false, 65001 ),
// Začátky a konce jednotlivých částí
Oddíl = List.Generate(
() => [L={"=", "Sterilisation", "Date", "End of cycle"}, i=0, r = -1],
each <= List.Count([L]),
each [
L = [L],
r = List.PositionOf(File, List.FindText(File,[L]{}){0}?),
i = + 1
],
each [r]
),
//
// první část - batch
Batch = [Batch = Text.AfterDelimiter( File{Oddíl{1}}, "_", {0, RelativePosition.FromEnd})],
//
// druhá část - parametry
ParamList = {"P4021","P4001","P4004","P4005","P4002","P4006","P4007","P4008","P4009","P4015","P4011","P4012"},
ParametryData = Table.FromList(List.FirstN(List.Skip(File, #"Oddíl"{2}+1), #"Oddíl"{3} - #"Oddíl"{2}-1), each {_}, {"Param"}),
#"Rozdělit parametry" = Table.SplitColumn(ParametryData, "Param", Splitter.SplitTextByWhitespace(QuoteStyle.Csv), {"Param.1", "Param", "Value"}),
#"Odebrané sloupce" = Table.RemoveColumns(#"Rozdělit parametry",{"Param.1"}),
#"Změněný typ s národním prostředím" = Table.TransformColumnTypes(#"Odebrané sloupce", {{"Value", type number}}, "en-150"),
#"Záhlaví se zvýšenou úrovní" = Table.PromoteHeaders(
// tabulku měníme na záznam
Table.Transpose(#"Změněný typ s národním prostředím"), [PromoteAllScalars=true]),
Parametry = Table.First(Table.SelectColumns(#"Záhlaví se zvýšenou úrovní",ParamList)),
//
// třetí část
// data mají pevnou pozici
FileData = Csv.Document(Lines.ToText(List.FirstN(List.Skip(File, #"Oddíl"{3}+1), #"Oddíl"{4} - #"Oddíl"{3}-1)),13,{2,12,21,26,33,39,45,51,57,62,69,75,81},ExtraValues.Ignore,65001),
#"Přejmenované sloupce" = Table.RenameColumns(FileData,
List.Zip(
{
Table.ColumnNames(FileData),
List.RemoveNulls(Text.Split(File{#"Oddíl"{3}}, " "))
})),
#"Odebrané sloupce1" = Table.RemoveColumns(#"Přejmenované sloupce",{"PT04(C)", "PT05(C)", "Operator", "PR01(kPa)", "PT01(C)", "PT03(C)", "Circulator(ON/OFF)"}),
OdebranéPrázdnéŘádky = Table.SelectRows(#"Odebrané sloupce1", each ([Date] <> "")),
//
// Některé příkazy mění pořadí a z dat původní pořadí nejde určit
#"Přidané: Index" = Table.AddIndexColumn(OdebranéPrázdnéŘádky, "Index", 0, 1, Int64.Type),
DateHour = Table.CombineColumns(#"Přidané: Index",{"Date", "Hour"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateHour"),
#"Vyčištěný text" = Table.TransformColumns(DateHour,{
{"Phase", Text.Trim, type text},
{"PR03(kPa)", each Text.Combine(List.RemoveNulls(Text.Split(_, " ")),"")}}),
#"Změna typu - DateHour" = Table.TransformColumnTypes(#"Vyčištěný text",{{"DateHour", type datetime}}),
Data = Table.TransformColumnTypes(#"Změna typu - DateHour", {{"Phase", type text}, {"PR03(kPa)", type number}, {"PT02(C)", type number}, {"Humidity(%)", type number}}, "en-US"),
//
// Data seskupíme a vyberemo potřebná
//
#"Seskupené řádky" = Table.Group(Data, {"Phase"}, {
{"Tab",
each Table.SelectRows(_, (r) => r[Index] = List.Min([Index])),
type table [DateHour=nullable datetime, Phase=nullable text, #"PR03(kPa)"=nullable number, #"PT02(C)"=nullable number, #"Humidity(%)"=nullable number, Index=number]
},
{"Počet řádků",
each Table.RowCount(_),
Int64.Type},
{"PT02 (Max)",
each List.Max([#"PT02(C)"]),
type number},
{"PT02 (Min)",
each List.Min([#"PT02(C)"]),
type number},
{"PT02 (Prům)",
each List.Average([#"PT02(C)"]),
type number}
}),
#"Rozbalené Tab" = Table.ExpandTableColumn(#"Seskupené řádky", "Tab", {"DateHour", "PR03(kPa)", "PT02(C)", "Humidity(%)"}, {"DateHour", "PR03(kPa)", "PT02(C)", "Humidity(%)"}),
GroupData = Table.Buffer(Table.AddKey(#"Rozbalené Tab", {"Phase"}, true)),
//
// Máme připravené souhrny
// teď výpočty - připravíme funkce
D1 = (_) => _{[Phase="2.9"]}?[#"PR03(kPa)"]?,
D5 = (_) => _{[Phase="2.9"]}?[#"PR03(kPa)"]?,
D8 = (_) => _{[Phase="2.1"]}?[#"PR03(kPa)"]?,
D11 = (_) => _{[Phase="6.9"]}?[#"PR03(kPa)"]?,
D13 = (_) => _{[Phase="8.9"]}?[#"PR03(kPa)"]?,
D3 = (_) => _{[Phase="3.9"]}?[#"Humidity(%)"]?,
D6 = (_) => _{[Phase="2.9"]}?[#"PT02(C)"]?,
D7 = (_) => _{[Phase="3.9"]}?[#"PR03(kPa)"]? - _{[Phase="2.9"]}?[#"PR03(kPa)"]?,
D12 = (_) => _{[Phase="6.9"]}?[#"PR03(kPa)"]? - _{[Phase="4.9"]}?[#"PR03(kPa)"]?,
D2 = (_) => _{[Phase="2.9"]}?[#"DateHour"]? - _{[Phase="2.1"]}?[#"DateHour"]?,
D4 = (_) => _{[Phase="3.9"]}?[#"DateHour"]? - _{[Phase="2.9"]}?[#"DateHour"]?,
D9 = (_) => _{[Phase="4.9"]}?[#"DateHour"]? - _{[Phase="3.9"]}?[#"DateHour"]?,
D10 = (_) => _{[Phase="6.9"]}?[#"DateHour"]? - _{[Phase="4.9"]}?[#"DateHour"]?,
D14 = (_) => _{[Phase="10.9"]}?[#"Počet řádků"]?,
CalculatedData =
[D1 = D1(GroupData)] &
[D2 = D2(GroupData)] &
[D3 = D3(GroupData)] &
[D4 = D4(GroupData)] &
[D5 = D5(GroupData)] &
[D6 = D6(GroupData)] &
[D7 = D7(GroupData)] &
[D8 = D8(GroupData)] &
[D9 = D9(GroupData)] &
[D10 = D10(GroupData)] &
[D11 = D11(GroupData)] &
[D12 = D12(GroupData)] &
[D13 = D13(GroupData)] &
[D14 = D14(GroupData)]
,
// spojíme záznamy
CombineTables = Table.FromRecords({Batch & Parametry & CalculatedData}),
#"Duration na time" = Table.TransformColumns(CombineTables,{{"D2", each #time(0,0,0) + _, type time}, {"D4", each #time(0,0,0) + _, type time}, {"D9", each #time(0,0,0) + _, type time}, {"D10", each #time(0,0,0) + _, type time}}),
#"Zmenený typ" = Table.TransformColumnTypes(#"Duration na time",{{"Batch", type text}, {"P4021", type number}, {"P4001", type number}, {"P4004", type number}, {"P4005", type number}, {"P4002", type number}, {"P4006", type number}, {"P4007", type number}, {"P4008", type number}, {"P4009", type number}, {"P4015", type number}, {"P4011", type number}, {"P4012", type number}, {"D1", type number}, {"D2", type time}, {"D3", type number}, {"D4", type time}, {"D5", type number}, {"D6", type number}, {"D7", type number}, {"D8", type number}, {"D9", type time}, {"D10", type time}, {"D11", type number}, {"D12", type number}, {"D13", type number}, {"D14", type number}})
in
#"Zmenený typ"[/code]
Ta ukázka je makro nebo dotaz v powerquery?
ben59 napsal/a:
... Po chvíli hledání našel jiný způsob zápisu a vše je OK.
Místo "..VLookup(Range("List1!A2"),.."
nově ".. VLookup(List1.Range("A2"),..,
Děkuji za nasměrování.
Pokud je List1 = Codename tak ano. Pokud je List1 = Name (to co je vidět na záložce listu) a Codename je jiné, tak je radost dočasná.
Co aggregate?
=AGGREGATE(14;3;$D$6:$D$8;2)
Pokud máte novější verzi Excelu, tak třeba:
=SORT(UNIQUE(FILTER(A1:A12;NE(JE.PRÁZDNÉ(C1:C12)))))
(zadá se do buňky, sám se natáhne na potřebnou velikost)
Otázka je jednoduchá, ale popis je na článek. V excelu těch možných aktualizací je docela dost a způsoby se v různých případech liší.
Napovím aspoň směr pátrání.
Nejjednodušší je v makru před uložením prostě počkat nějakou dobu, je to jednoduché, univerzální a po vyladění doby i přiměřeně spolehlivé.
V některých případech může pomoci DoEvents.
U řady objektů je problém s aktualizací na pozadí (pq, kont. tab, ...). Tady je nutné aktualizaci na pozadí zakázat - u různých objektů se to dělá různě - aktualizovat a obnovit nastavení.
Aby to bylo zábavnější, např. při aktualizaci dotazů pq, se při aktualizaci jednoho dotazu aktualizují i všechny zdrojové dotazy, pokud existují, bez ohledu na to, kdy byly akualizované, tj. běžně i několikrát. Tady obvykle ve smyčce kontroluju, jestli aktualizace doběhla.
elninoslov napsal/a:
Ak má CSV stĺpce oddelené bodkočiarkami/středníkmi/";" tak žiadny iný text v CSV NESMIE obsahovať tieto oddeľovače.
Pro načtení použijte powerquery, to to načte správně, jeho chování je možné v případě potřeby korigovat. Zbytek pak jak je libo powerquery, vba, ...
Je volno?:
=A1<>WORKDAY(A1-1;1;Svátky)
A1 - datum
Svátky - tabulka svátků
Pokud je tabulka načtena přes power query, tak je nejjednodušší ve volbě "uložit do ..." vybrat kontingenční tabulku ...
Pokud je tabulka opravdu velká (tak 10^5 a více záznamů) tak je lepší ji vložit do datového modelu a doplnit dimenze (kalendář, místa, druh) propojit relacemi a v kt pak tyto dimenze použít na identifikaci řádků/sloupců místo sloupců v datové tabulce.
Vyber oblast s daty na listu "CN Codes".
Dej vložit kont tabulku. V dialogu zaškrtni "Přidat data do datového modelu".
Do Řádků vlož "CN Codec 2021".
v záložce "powerpivot" vyber míry/nová míra.
pojmenuj míru např. "Hodnoty C"
do pole se vzorcem vlož
=CONCATENATEX(VALUES(Rozsah[Dual Use Codification]); Rozsah[Dual Use Codification]; ", ")
Uprav názvy - Rozsah je název tabulky, může být jiný, jména sloupců (tady jsou 2 mezery vedle sebe...)
Míra se objeví v seznamu polí tabulky. Přidej ji do hodnot.
Hotovo.
Nebo třeba:
=J2-VYHLEDAT(DENTÝDNE(G2;2);{1;4;6};{0;48;24})
Pokud je struktura souborů stejná, tak stará dobrá příkazová řádka.
Vše spojí do jednoho souboru copy
copy *.csv suma.csv
Pokud je tam hlavička, tak pomůže find (nejprve vybrat hlavičku), pak kopírovat bez hlavičky.
Na více souborů je for: for %s in (*.csv) do find /v "hlavicka" %s >> suma.csv
více v online nápovědě.
Uživatelské menu
\nMenu
Formulář Faktura
Formulář Faktura IV
Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.
Helios iNuvio
Používáte podnikový systém Helios iNuvio? Potřebujete pomoci se správou nebo vyvinout SQL proceduru? Více informací naleznete na stránce Helios iNuvio.
RSS nejnovější články