< návrat zpět

MS Excel


Téma: Index - každý rok začít od 1 Power Query rss

Zaslal/a 20.5.2024 10:51

AlfanDobrý den,
mám dotaz ohledně následujícího příkladu.
Ve sloupci "Period" mám datum ve formátu datum a čas.
Potřebuji pro každý záznam přiřadit pořadové číslo.
Ano, seřadím si vzestupně a přiřadím Index a mám pořadí.
Jenže já bych potřeboval vyřešit to, že ve sloupci "Period" jsou třeba 2 roky nebo pak mohou být i 3 roky i více a já potřebuji, aby ten Index (pořadí) začínal v každém roce od čísla 1.
Vycházím z toho, že mám ten sloupce "Period" seřazený, jak jsem psal.
Sice mě napadlo, že bych to mohl udělat porovnáním se sloupcem "Y", kde bych zjistil, že záznam je už o 1 větší a pak bych začal znovu indexovat, ale bohužel si s tím neporadím.
Mohl byste mi někdo, prosím, s tím pomoct?
Děkuji.

Příloha: zip56537_index_wall.zip (120kB, staženo 7x)
Zaslat odpověď >

#056538
avatar
Takto?
Nechal jsem jen vytvořený dotaz nenačtený do listu excelu.
Příloha: zip56538_56537_index_wall.zip (133kB, staženo 6x)
citovat
#056539
Alfan
Koukal jsem na to.
Tento postup je pro 2 roky 2023 a 2024.
Já bych, ale potřeboval nějaký "univerzální" řešení, pokud to jde.
Totiž ta zdrojová data z tabulky "dataf" mají samozřejmě více sloupců.
Dá se udělat nějaký postup, aby to právě zkontrolovalo ty roky a jakmile by byl rok na řádku nad aktuálním řádkem jiný (nižší) automaticky by to začalo indexovat do doby, než by to opět našlo stejný případ, kdyby bylo více roků než 2?citovat
#056540
avatar
Tak:

let
Zdroj = Excel.CurrentWorkbook(){[Name="dataf"]}[Content],
#"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"Period", type datetime}}),
#"Seřazené řádky" = Table.Sort(#"Změněný typ",{{"Period", Order.Ascending}}),
#"Přidané: Index" = Table.AddIndexColumn(#"Seřazené řádky", "Index", 1, 1, Int64.Type),
Y = Table.AddColumn(#"Přidané: Index", "Y", each DateTime.ToRecord([Period])[Year], Int64.Type),
#"Seskupené řádky" = Table.Group(Y, {"Y"}, {{"tab", each _, type table [Period=nullable datetime, Index=number, Y=number]}}),
#"Odebrané sloupce" = Table.RemoveColumns(#"Seskupené řádky",{"Y"}),
Index.2 = Table.TransformColumns(#"Odebrané sloupce",{{"tab", each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)}}),
#"Rozbalené tab" = Table.ExpandTableColumn(Index.2, "tab", {"Period", "Index", "Y", "Index.1"}, {"Period", "Index", "Y", "Index.1"})
in
#"Rozbalené tab"


Tohle je o malinko kratší, ale hůř se to "nakliká":
let
Zdroj = Excel.CurrentWorkbook(){[Name="dataf"]}[Content],
#"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"Period", type datetime}}),
#"Seřazené řádky" = Table.Sort(#"Změněný typ",{{"Period", Order.Ascending}}),
#"Přidané: Index" = Table.AddIndexColumn(#"Seřazené řádky", "Index", 1, 1, Int64.Type),
Y = Table.AddColumn(#"Přidané: Index", "Y", each DateTime.ToRecord([Period])[Year], Int64.Type),
#"Seskupené řádky" = Table.Group(Y, {"Y"}, {{"tab",
each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type),
type table [Period=nullable datetime, Index=number, Y=number, Index.1 = number]}}),
#"Odebrané sloupce" = Table.RemoveColumns(#"Seskupené řádky",{"Y"}),
#"Rozbalené tab" = Table.ExpandTableColumn(#"Odebrané sloupce", "tab",
{"Period", "Index", "Y", "Index.1"})
in
#"Rozbalené tab"
citovat
#056541
Alfan
@lubo
Můžeš mi pls vysvětlit ten postup?
1) Přiřadíš Index.
2) Vyfiltruješ rok
3) Seskupíš podle "Y"
4) Odebereš sloupec "Y"
5) Aha, ty jsi tam vložil řádek, který oindexoval každou "tab" samostatně a pojmenoval jsi to Index.1
To jsi musel "natvrdo" napsat v "Rozšířeném editoru" nebo to jde nějak naklikat, ten bod 5?

Tak to neumím, zapsat to, že pro každou tabulku a vložit index.

Každopádně děkuji.citovat
#056542
avatar
do původních dat jsem na řádku 5 přidal datum s rokem 2025 a další dva sloupce,
Zkuste si přidat další sloupec případně datum a aktualizovat dotaz Výstup který vytváří tabulku na listu Výstup
teď by mělo být jedno kolik sloupců bude ve zdroji, všechny se objeví ve výstupu
Příloha: zip56542_56537_index_wall.zip (298kB, staženo 4x)
citovat
#056543
avatar
V podstatě je to stejné, jako postup od venyho.

Tabulka se rozdělí do více tabulek podle roku (funkce Table.Group) ve druhém sloupci výsledné tabulky jsou v buňkách tyto tabulky. A do každé tabulky s jedním rokem se přidá index (od jedné).

Pak se tabulky zase spojí do jedné.

veny přidal index ve funkci Table.AddColumns, tady je to v prvním případě v Table.TransformColumns, ve druhém rovnou v Table.Group (při seskupování).

Pak se tabulky spojí (rozbalí se sloupec s tabulkami)

Klikání do prázdného místa v políčku s "table" ukáže obsah tabulky v jednotlivých krocích.citovat
#056544
Alfan
Děkuji oběma.

Já jen nevím, jak ten bod 5)
Když to vidím o tebe napsané, tak to chápu.
Ale to jsi musel napsat se znalostí zápisu přímo v Editoru?
Jde mi o ten krok "Index.2"
Ten bych nedal, neuměl napsat z hlavy.

Každopádně si toto řešení uložím k případnému dalšímu využití.
Ještě jednou díky moc oběma.citovat
#056545
Alfan
A krok Index.2 jste tedy vložil "natvrdo", napsal v Rozšířeném editoru?
= Table.TransformColumns(#"Odebrané sloupce",{{"tab", each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)}}),citovat
#056547
avatar
Naklikat jde obecně jen omezený počet výpočtů. Ale dá se naklikat základ, který je pak snadné upravit.

Funkce Table.Group má ve třetím parametru seznam, ve kterém je ve vnořených seznamech předpis pro výpočet jednotlivých sloupců. Vždy je to jméno sloupce a funkce, která vypočte hodnotu sloupce pro každý řádek.
Předávaným parametrem je tabulka, která obsahuje všechny řádky původní tabulky, které patří do skupiny.
(funkce "each _" tedy vrací tuto tabulku. )

// máme tabulku rozdělenou podle roku:
#"Seskupené řádky" = Table.Group(Y, {"Y"}, {{"tab", each _, type table [Period=nullable datetime, Index=number, Y=number]}}),
// Klik na text "Table" v prvním řádku (pokud je to klik do prázdného místa buňky, tak se tabulka jen ukáže dole):
#"2023" = #"Seskupené řádky"{[Y=2023]}[tab],
// Přidáme Index roku
#"Přidané: Index1" = Table.AddIndexColumn(#"2023", "Index.1", 1, 1, Int64.Type),
// a máme potřebný kód funkce
// Vytvoříme funkci, kde tabulka je v proměné "_" a pojmenujeme ji třeba #"fceIndexRoku":
fceIndexRoku = each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type),
// opravíme funkci, která vrací sloupec "tab" ve funkci Table.Group. Tady je to změněno na dalším řádku:
#"Seskupené řádky_2" = Table.Group(Y, {"Y"}, {{"tab", fceIndexRoku, type table}})
// místo názvu funkce můžeme vložit vygenerovaný kód funkce: each Table.AddIndexColumn(_, "Index.1", 1, 1, Int64.Type)
//....
// odstraníme pomocné kroky
citovat
#056553
Alfan
Díkycitovat

Uživatelské menu

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

Menu

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.

On-line nástroje