MS Excel

Dobrý den,
chci se zeptat na zdroje dat v Power Query a na cestu k nim.

Zdroje dat si namapuji, buď jsou to soubory z jedné složky a další třeba je konkrétní *.xlsx sešit.

Pak se mi automaticky zapíše v PQ Absolutní cesta.

Já bych si to pak rád nějak zeditoval na cestu Relativní.
Mám na mysli třeba konkrétně u té složky, aby tam bylo napsáno něco obdobného:
"..\data\"

Absolutní cesta je tam třeba tato:
= Folder.Files("C:\Users\alfan\Documents\Deník\data")

Děkuji.
Radek

Zaslat odpověď >

Strana: « předchozí 1 2 3 4 5 6 další »

Alfan(23.4.2024 8:37)#056461

Jj, to je skoro ono.
Ten dotaz „PQ_Priklad3“ je dobrý, ale…
Já vůbec nerozumím tomu „fncParameter“ a ani nevím, jak to udělat.

Potřeboval bych do dotazu napsat, jako „Zdroj“, aby to bralo informaci z tabulky „PathNew“, viz příloha.

Když bych chtěl jen jeden soubor (to umím napsat), tak bych použil tabulku „Path“:

Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name = "Path"]}[Content][Path]{0}), null, true)

A pak se chci zeptat, když v tom zdrojovém adresáři budou třeba všechny soubory v *.xls, jak nastavím jeden, který bude první soubor, de facto ten první transformovaný?
Když si to dělám manuálně, tak ho mohu určit.

A při importu si to vybere vždy první list souboru automaticky?

Děkuji.

Příloha: zip

56461_otd-results_wall.zip (54kB, staženo 5x)

citovat

elninoslov(23.4.2024 9:35)#056462

xls nie je to isté ako xlsx
S xls bude problém, že musíte vedieť názov listu, lebo ich názvy nevráti v poradí ako sú v zošite.

Vyskúšajte si to aktivovaním/deaktivovaním (zrušením/pridaním "//") jedného alebo druhého riadku
#"Data príklad"

Příloha: zip

56462_cesta.zip (75kB, staženo 2x)

citovat

lubo(23.4.2024 10:11)#056463

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),
"\")citovat

Alfan(23.4.2024 10:11)#056464

Jsem "lama", měl jsem na mysli *.xlsx.

Jak píšete, tak u *xlsx není problém s názvy listů.

Potřeboval bych tedy vědět, co napsat do Dotazu tak, aby to načetlo *xlsx z adresáře.
Můžete mi to prosím pomoct?
Děkuji.citovat

lubo(23.4.2024 10:15)#056465

Alfan napsal/a:

Jak píšete, tak u *xlsx není problém s názvy listů.

Na to bych nespoléhal.citovat

elninoslov(23.4.2024 10:22)#056466

Medzitým, ako ste sem napísali, som urobil návrh na XLS s použitím nejakého identifikátoru v liste o ktorý ide. Môže to byť hocičo niekde v liste v bunke napr. "Report", alebo názov mesiaca, alebo niečo ... tu je to "X". Už to sem dám, keď to mám hotové. Ak je u Vás ten list takto identifikovateľný, pridám potom aj na XLSX.

Příloha: zip

56466_cesta_xls_id.zip (76kB, staženo 3x)

citovat

Alfan(23.4.2024 10:52)#056467

@elninoslov
Jj, já skoro ve 100 % případů načítám vždy první list v tom souboru, respektive, ono tam ani více listů nebývá.
Tak jestli by nebylo dobré to nastavit na první list v pořadí?
Ten identifikátor by tam musel někdo dopisovat.
A asi v 95 % případů používám *.xlsx.
Každopádně, když to budu mít i pro *.xls, tak jedině dobře.

@lubo
čtu to a testuji, ale jsem z toho nějaký zmatený, protože nejsem takový profík.
ani se mi nepodařilo v "Novém dotazu" zapsat tu vaši funkci "fnParametr", prostě mi to nejde.

Zatím díky oběma.citovat

elninoslov(23.4.2024 19:33)#056471

No musíme začať asi od znovu, čo vieme (sa domnievame), ďalej otázkami a odpoveďami.

1. ako zdroj potrebujete pre rôzne PQ dotazy
a) aj celú cestu k aktuálnemu súboru, v ktorom ten PQ dotaz je (napr. na získanie dát, ktoré nie sú Tabuľka)
b) aj cestu k adresáru aktuálneho súboru, v ktorom ten PQ dotaz je (napr. na získanie cesty podadresára umiestneného v adresári spolu s týmto súborom)

2. potrebujete načítať dáta z viacerých súborov v tom podadresári, a spojiť načítané dáta do jednej Tabuľky

3. súbory v podadresári sú XLSX, no môže sa stať, že budú aj XLS

4. údaje v súboroch v podadresári sú netabuľky (obyčajné dáta)

5. nie je možné sa spoliehať na to, že údaje načítate z 1. listu, lebo PQ Vám nedá zoznam listov v takom poradí, ako máte v Exceli.

Otázky:
6. Názov listu v súboroch v podadresári je vždy rovnaký?

7. Poznáte názov toho listu?

8. Ak nepoznáte názov toho listu, viete ho nejako od iných listov identifikovať? Nemusíte tam nič do súborov dopisovať. Veď je nejako predsa odlíšiteľný od ostatných listov. Napr. má vždy v bunke A1 napísané "Report" alebo "Výdaje" ..., pričom v iných listoch v súbore je vždy v bunke A1 niečo iné (napr. prázdna, "Jméno", "Souhrnná tabulka", ...).

9. Názvy stĺpcov sú rovnaké?

10. Dáta majú hlavičku v 1. riadku, alebo je nejaký offset (pevný, variabilný - ako ho identifikovať ?{napr. hlavička začína od riadku kde v stĺpci A je "Jméno"}), alebo nemajú hlavičku?

11. Ak neplatí bod 2. tak: Vyššie ste spomínal "první soubor", tým myslíte nájsť najnovší súbor v danom podadresári podľa dátumu modifikácie?citovat

Alfan(24.4.2024 7:44)#056475

Odpovědi, viz příloha.
Děkuji.

Příloha: zip