< návrat zpět

MS Excel


Téma: Power Query vychytávky rss

Zaslal/a 8.6.2019 10:34

Ahoj,
začínám se věnovat a učit se PQ v excelu, jen tak pro sebe, a narážím na spoustu nejasností a potíží. Zkoumám práce především od mepexg, ale i jiné zdroje, návody a postupy na www, něco se podle nich daří, něco ne.
Přijde mi podivné, že pro mě základní věc, jako zdroj dat ze složky s relativní cestou od souboru není jako běžná funkce a musí se poměrně složitě dopracovat v kódu. A to se týká i dalších funkcí toho M jazyka, že nejsou naklikatelné stejně jako výběr funkce v excelu. Nebo mi něco třeba uniká, nevím...
Dávám do přílohy soubor, kde
1) nabízím řešení takové drobné úpravy v PQ pro chytré sloučení všech sloupců tabulky - nic extra, jen jsem na to narazil při svém učení a přijde mi to názorné, jak běžně zaznamenaný postup lze dále dotvářet v rozšířeném editoru, takové pro mě první nakopnutí, co by všechno mohlo jít ... (zdroj je uvedný v souboru, www.mrexcel.com .... )
2) je tam popsaný můj dotaz na chování průřezu (sliceru) v excelu 365, kdy ve své verzi mám stále stejnou ikonu filtru bez ohledu na jeho stav a štve mě to - jde to někomu správně?

Příloha: xlsx43500_pq_slouceni_sloupcu_chytre.xlsx (114kB, staženo 36x)
Zaslat odpověď >

#043502
MePExG
Dobrý deň. Zlučovanie (univerzálne pre rôzny počet) stĺpcov do jedného sa mi nezdá dákou vychytávkou, ale dá sa. Väčšinou zlučovanie som použil (na konkrétne stĺpce) pri transformácii tabuľky, ale tvorbe jednoznačného kľúča. Načítanie údajov z cesty robím tak, že najskôr vytvorím postup pre konkrétnu cestu. Potom v MSE vytvorím pomenovanú bunku s funkciou cell a z tejto načítam obsah a odstránim všetky kroky okrem prvého a vyklikám zmenu na text a vrátenie načítanej hodnoty. Z toho urobím funkciu a do hlavného dotazu načítam hodnotu z funkcie do premennej a cestu nahradím premennou. Viac je zaujímavé robiť rôzne transformácie, vyhľadávanie v PQ, unpivotovanie tabuliek, generovanie hodnôt, atď. Čarovné je využitie číslovania Index spolu s modulo, resp. celá časť po delení. Nebaví ma učiť sa syntax a všetky funkcie PQ, preto skúmam a učím sa postupy a tie, ktoré sú zaujímavé a nedajú sa vyklikať si zapisujem. Veľmi rád používam PBI od jedného pána, v ktorom sa dá ľahko dohľadať funcia, syntax a príklad https://community.powerbi.com/t5/Data-Stories-Gallery/Bemint-Power-Query-M-Functions-dashboard/m-p/316417. Prajem hodne úspechov 1 . Ozaj a k tým slicerom robí to aj mne, ale optimalizáciou obsahu (nefiltrovať dátum, ale rozdeliť na RMD), sa to dá zvládnuť.citovat
#043505
avatar
Jistě že sloučení všech sloupců je k ničemu, důležitý je ten postup úpravy, až když jsem si to barevně rozebral ve Wordu, tak jsem to viděl názorně. A dal jsem to sem, abych taky něco poskytnul jiným začátečníkům. A založil to vlákno už s úmyslem pokračovat.
To co jsem chtěl postupně vymámit od Vás, už jste mi zodpověděl, ten postup na relativní cestu. Ono z hotového souboru se to těžko pochopí. Díky.
A zamýšlel jsem dát další, ten hlavní dotaz na Power Query, tak tady je.
Zase jen pro sebe a učení v PQ chci z bank.výpisu získat platby v Tescu, kde bude na 1 řádku Datum zaúčtování, částka, datum placení. Tak si to léta zapisuju ručně do excelu, chci tedy stejné schéma jako výstup. Pdf si v Acr.Reader přeuložím jako txt a mám zdroj. Ale každá platba je ve více řádcích pod sebou. To se pravidelně opakuje, dají se jednoznačně rozeznat a filtrovat řádky s potřebnými údaji a vše ostatní jde odstranit. Pak mám sloupec co 1 platba, to 3 řádky.
Jediný postup co mi funguje, je transponovat sloupec do řádku, sloučit sloupce po 3 s klíč.oddělovačem (Tabulátor), transponovat zpět na sloupec a nechat řádky s tím Tabulátorem. Tak mám všechny potřebné hodnoty 1 platby v 1 řádku. A pak je dál z toho vydolovat už jde. Takže částečně úkol splněn, ALE...
Problém je počet plateb, já to mám dopředu machystané na 30 za měsíc (v rozš.editoru ctrl+c, ctrl+v), když jich je míň, chybové sloupce se odstraní, ale když jich je víc, nijak to nepoznám a poslední platby chybí.
Zkuste se někdo podívat, jak líp udělat to spojení tří řádků do jednoho, aby to vzalo nějak chytře správný počet, nějaké opakování, cyklus, nevím. Včera celý den jsem zkoušel různé postupy: odebírat střídavé řádky, udělat 3 dotazy a slučovat, připojovat, NIC, pořád mám ty data na konci pod sebou.
Příloha: zip43505_data-z-txt.zip (23kB, staženo 21x)
citovat
#043509
MePExG
Tu je moje riešenie (bez obmedzenia počtu riadkov) a zobrazenie v kt. Postup najskôr Zoznam (súbory z adresára), referencia do Data, (rozkliknutie dvojšípky Content) = combine tj. vytvorenie štruktúry dotazov, úprava Trnasform sample file, oprava v dotaze Data (odstránenie posledného riadku change type a nahradenie správnym), vytovrenie pomenovanej bunky kde je adresár=>tvorba dotazu a úprava na fciu, do Zoznam doplnenie do premennej volanie fcie cesta a nahradenie skutočnej cesty premennou.
Příloha: xlsx43509_spracuj.xlsx (36kB, staženo 35x)
citovat
#043513
avatar
Díky za Vaše řešení, skvělá práce! Je to víc než jsem chtěl dostat, takové kompletní řešení. Super. Už jsem nastudoval postup, jak si připravit sloupce a čísla pro Kontingenční sloupec (u nás česky) a dostat tak data do řádku. Perfektní, to bych sám nedal.
Zjišťuju, že je mi překážkou rozdílné pojmenovávání v CZ excelu proti ENG, (kroků a sloupců), kód je naštěstí stejný, ale podle něj těžko nacházím odpovídající ikony v menu. Ale to časem půjde...citovat
#043562
avatar
Můžete mrknout na alternativní řešení skládání sloupců do řádků.
Kód je o něco kompaktnější, možná pro začátečníka trochu obtížnější.


// Transform File from Data
let
Zdroj = (#"Sample File Parameter1" as binary) => let
Source = Table.FromColumns({Lines.FromBinary(#"Sample File Parameter1", null, null, 1250)}),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Column1], "Období:")),
Year = Text.End(Text.Trim(#"Filtered Rows"{0}[Column1]),5),
Vlastní1 = Source,
#"Doplněn Dat.zaúčt" = Table.TransformColumns(Vlastní1,
{
{"Column1",
each try "Dat.zaúčt: " & Text.From(Date.FromText(Text.Start(_, 6) & Year)) otherwise _,
type text}}),
#"Rozdělit sloupec oddělovačem" = Table.SplitColumn(#"Doplněn Dat.zaúčt", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Pole", "Data"}),
#"Filtrované řádky" = Table.SelectRows(#"Rozdělit sloupec oddělovačem", each ([Pole] = "Dat.zaúčt" or [Pole] = "Místo" or [Pole] = "Částka")),
#"Přidané: Index" = Table.AddIndexColumn(#"Filtrované řádky", "Index", 0, 1),
#"Nahrazená hodnota" = Table.ReplaceValue(#"Přidané: Index",
each [Index],
each if [Pole] = "Místo" then [Index] -1
else if [Pole] = "Částka" then [Index] -2
else [Index],
Replacer.ReplaceValue,{"Index"}),
#"Převedený sloupec" = Table.Pivot(#"Nahrazená hodnota", List.Distinct(#"Nahrazená hodnota"[Pole]), "Pole", "Data"),
#"Filtrované řádky1" = Table.SelectRows(#"Převedený sloupec", each ([Místo] <> null)),
#"Rozdělit sloupec oddělovačem1" = Table.SplitColumn(#"Filtrované řádky1", "Částka", Splitter.SplitTextByDelimiter("CZK", QuoteStyle.Csv), {"Částka", "Dat. platby"}),
#"Změněný typ - Částka" = Table.TransformColumnTypes(#"Rozdělit sloupec oddělovačem1",{{"Částka", Currency.Type}}, "en-US"),
#"Změněný typ - Datum" = Table.TransformColumnTypes(#"Změněný typ - Částka",{{"Dat.zaúčt", type date}, {"Dat. platby", type date}}),
#"Odebrané sloupce" = Table.RemoveColumns(#"Změněný typ - Datum",{"Index"})
in
#"Odebrané sloupce"
in
Zdroj
citovat

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