• Úvod
• Excel návod
• Excel šablony
• Nástěnka
• Mobility
• Tipy na výlet
• Galerie
• Fórum

Příspěvky uživatele

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 <b> <> 1000),
#"Seskupené řádky" = Table.Group(#"Najít první 1000", {"b"},
{
{"start", each _<c>{0}, type time},
{"jedna", each Table.Skip(_, each <b> <> 1)<c>{0}?, type time},
{"Doba", each Table.Skip(_, each <b> <> 1)<c>{0}? - _<c>{0}, type duration}
},
GroupKind.Local,
(x,y) => if (x<b> = 1000) and (y<b> <> 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 <b> 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, ...)

analýza kontingenční tabulky - změnit zdroj dat - použít externí zdroj dat / zvolit připojení.

Když dávám do pq něco podobného, používám obecnější přístup:

Sloupce = {"A", "B", "C", "D", "E", "F", "B"},
Porovnání = Table.AddColumn(
Zdroj,
Text.Combine(Sloupce, ";"), // Jména sloupců v názvu
each List.Accumulate(
List.Skip(Sloupce, 1), // Od druhého prvku
[x = Record.Field(_, Sloupce{0}), vys = ""],
(z, a) => [
x = Record.Field(_, a),
vys = z[vys] & (if z[x] = x then "1" else "0")
]
)[vys],
type text
)
V seznamu Sloupce jsou porovnávané sloupce v potřebném pořadí, mohou být uvedeny i opakovaně.

Uživatelské menu

Nejste přihlášen(a)

• přihlásit
• registrace

\n

Menu

On-line nástroje

Formulář Faktura

Aktivní diskuse