Příspěvky uživatele

Podle nejlepší praxe MS: HSTACK = SROVNAT.VODOROVNĚ

Něco je přeloženo, něco není, na stránkách MS je to často chybně. Navíc AI od ms se "učí" z vlastních chyb, tj. opakuje je a tvrdí, že je nutné si pořídit poslední verzi SW...

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

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.

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"

Proč to neuděláte ve wordu? - Hromadný tisk, data z excelu...

Jako vždy, je možností docela dost.
Pokud jse o to, aby bylo možné sloupce zadávat jednoduše,
nabízí se uložení jmen do seznamu, (zápisem , načtením z listu, ...).

Např.:
#"Jmena sloupců" = {"czk", "czk1", "czk3", "czk4", atd}

Jedno variantou je vytvořené seznamu parametrů pro Table.TransposeColumns pomocí funkce List.Transform:

var1 = Table.TransformColumns(Tabulka, List.Transform(#"Jmena sloupců", each {_, each _ * rate, type number})),

Totéž, ale snad srozumitelnější (první each je nahrazen explicitní deklarací funkce).

= Table.TransformColumns(Tabulka, List.Transform(#"Jmena sloupců", (s) => {s, each _ * rate, type number}))

Další možností je použití funkce Table.ReplaceValue, nahrazující funkci použijeme vlastní.

var2 = Table.ReplaceValue(Tabulka, 0, rate, (x,y,z) as number => x * z, #"Jmena sloupců")

Jsou i další možnosti (List.Accumulate např., ale to je tady málo efektivní)

Trochu zmatek v pojmech.

Výhodné je nevytvářet pro každý parametr samostatnou pojmenovanou buňku, ale shrnout je do tabulky.
V příkladu má tabulka 2 sloupce - Parameter = jméno parametru a Hodnota = jeho hodnota.

V kódu si tuto tabulku načtu (tblParam = jméno tabulky s parametry):

// ParametryTab
let
Zdroj = Excel.CurrentWorkbook(){[Name="tblParam"]}[Content]
in
Zdroj

tady do tabulky ParametryTab.

Parametry se pak vytahují nějakou funkcí (kvůli přehlednosti, omezení počtu odkazů, případně kvůli ošetření chyb, snadno se dá přidat další parametr, ...)

u mne tato funkce vypadá takto:

// fnParametr
(Parameter as text) =>
let
Hodnota = ParametryTab{[Parameter = Parameter]}?[Hodnota]
in
Hodnota

Pokud parametr není nalezen, vrátí null, pokud je v tabulce více než jednou, vyvolá chybu. Tu lze ošetřit.

Naplnění tabulky:

Obvykle je jedním z parametrů adresář, ve kterém je soubor:

Funkce excelu např.:

=TEXTPŘED(POLÍČKO("filename";$A$1);"[")[/code]

V případě umístění souboru někde na onedrive a pod. vrací url adresu. To se dá ošetřit.

Dalším běžným parametrem je jméno akr. souboru:
Vzorec excelu:

[code]=INDEX(ROZDĚLIT.TEXT(POLÍČKO("filename";$A$1);{"[";"]"});2)

Dalšími parametry jsou adresy potřebných adresářů:
Buď absolutně, nebo relativně např. k adresáři se souborem.

Potřebná cesta se pak poskládá.

Obdobně při práci s excelem do parametrů dávám názvy potřebných listů, tabulek, ...

Celou cestu k souboru pak dostanu

=Text.Combine(
{ fnParametr("parRootDir"),
fnParametr("parSubDir1"),
fnParametr("parSubDir2"), ...},
"\")

Nebo, abych si ušetřil psaní:

=Text.Combine(
List.Transform{"parRootDir", "parSubDir1", "parSubDir2", ...},fnParametr),
"\")

V tabulce jsem přidal jména sloupců "b" a "c".

Následující dotaz power query zahodí v tabulce řádky před prvním 1000, pak vybere ve sloupci "b" všechny 1000 a najde následující 1.
vrátí čas u 1000 a u 1 a jejich rozdíl.

let
Zdroj = Excel.CurrentWorkbook(){<Name="Tabulka1">}<Content>,
#"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"b", Int64.Type}, {"c", type time}}),
#"Najít první 1000" = Table.RemoveFirstN(#"Změněný typ", each <> 1000),
#"Seskupené řádky" = Table.Group(#"Najít první 1000", {"b"},
{
{"start", each _<c>{0}, type time},
{"jedna", each Table.Skip(_, each <> 1)<c>{0}?, type time},
{"Doba", each Table.Skip(_, each <> 1)<c>{0}? - _<c>{0}, type duration}
},
GroupKind.Local,
(x,y) => if (x = 1000) and (y <> 1000) then 0 else 1 ),
#"Odebrané ostatní sloupce" = Table.SelectColumns(#"Seskupené řádky",{"start", "jedna", "Doba"})
in
#"Odebrané ostatní sloupce"

Jak zapsat hranaté závorky? Tady je místo nich a <c>.

Chtělo by to srozumitelné vysvětlení.

Obecně svyhledat vyhledává hodnoty průchodem klíčů pokud je poslední parametr 0, v takovém případě hledá první výskyt klíče, pokud chybí nebo je 1 vyžaduje setříděné klíče a vrací poslední hodnotu.

+ Pokud je prohledávaná oblast 1 řádek, tak to taky nadává smysl

Vzorec může být jednodušší:

=ZABALSLOUPCE(B2:B55;6)

např. funkce Table.TransformColumns:

// zbozi
let
Zdroj = Excel.CurrentWorkbook(){[Name="zbozi"]}[Content],
#"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"název", type text}, {"czk", type number}}),
Rate = Table.TransformColumns(#"Změněný typ",{{"czk", each _ * rate, type number}})
in
Rate

// rate (vrátí hodnotu a ne tabulku)
let
Zdroj = Excel.CurrentWorkbook(){[Name="rate"]}[Content],
#"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"rate", type number}}),
rate1 = #"Změněný typ"{0}[rate]
in
rate1

V dialogu importovat daty lze vybrat sestavu kontingenční tabulky a neukládat do datového modelu. Potom lze zdroj (pq dotaz) měnit. (U mne to vygeneruje kopii dotazu pod novým jménem, je nutné udržovat názvy. Kde je dotaz použit je vidět ve vlastnostech dotazu)

Chování se může podle verze měnit.

Pokud jsou data v datovém modelu lze samozřejmě měnit obsah tabulky ručně, další alternativou je makro - zřejmě to jde. Testovat to nebudu.

Zřejmě by také šlo, s použitím dimenzí a měřítek měnit rozsah dat v tabulkách jedním filtrem - bylo by to dost práce.

Žádný způsob není snadný, zřejmě bude manuální změna nejsrozumitelnější (rozhodování mezi "jde to" x "umím to").

Chyba, jen připojení. Toto funguje, pokud je dotaz přímo zdrojem kt.

Mimochodem, proč v databázi v databázi ukládáte tabulky se stejnými daty?

Pokud máte kt z datového modelu, tak stačí kt zkopírovat a filtr nastavit v kt.

Jak jsou nastavený ty dotazy (je to jen připojení? - bez tabulky, modelu, ...)

Příspěvky uživatele

Uživatelské menu

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

Aktivní diskuse

kontrola buniek

KT jako text do emailu

vlastní pás karet

vlastní pás karet

KT jako text do emailu

odkaz chyba

odkaz chyba