MS Excel

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ě?

Zaslat odpověď >

MePExG(8.6.2019 14:12)#043502

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

. 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

Dingo(11.6.2019 9:09)#043505

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: zip

43505_data-z-txt.zip (23kB, staženo 22x)

citovat

MePExG(11.6.2019 20:10)#043509

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: xlsx

43509_spracuj.xlsx (36kB, staženo 37x)

citovat

Dingo(12.6.2019 13:59)#043513

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

lubo(19.6.2019 16:47)#043562

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

Téma: Power Query vychytávky

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje