Mal eine PQ Frage

[Egon12]

Hallo Miteinander,
 
ich habe da mal ein ganz spezielles Problem mit Einlesen von Tabellenblättern (später Namen) im PQ.

Mit: Table.SelectRows(Quelle, each [Kind] = "Sheet"),
[Kind] kann ich nur nutzen, wenn ich von außen aus Workbook zugreife via Pfad.
Via Excel.CurrentWorkbook(), ist [Kind] 0

Gibt es einen Weg via PQ die Tabellenblattnamen ohne von außen an die Datei zu gehen in eine Liste zu bekommen oder bleibt nur dieser Weg.
Für Interessierte der funktionierende M-Code:

Code:

let
    NurBlätter = Table.SelectRows(Excel.Workbook(File.Contents("C:\IrgendEinVerzeichnis\IrgendEinName.xlsx"), null, true), each [Kind] = "Sheet"),
    Gefiltert = Table.SelectRows(NurBlätter, each not List.Contains({"Blanco", "Vorgaben", "Auswertung"}, [Name])),
    NurNamen = Table.SelectColumns(Gefiltert, {"Name"}),
    Umbenannt = Table.RenameColumns(NurNamen, {{"Name", "Mitarbeiter"}})
in
    Umbenannt

Mit VBA ist das natürlich überhaupt kein Ding.
 
Gruß Uwe

[ws-53]

Du kannst entweder, die aktuelle Arbeitsmappe auf diese Art und Weise lesen, oder wenn du die Tabellenblätter zumindest als benannte Bereiche definiert hast, kannst du auch: "Excel.CurrentWorkbook()" verwenden. Im ersten Fall wird dann immer der Datenbestand der letzten Sicherung gelesen.

[Andreas Killer]

Gibt es einen Weg via PQ die Tabellenblattnamen ohne von außen an die Datei zu gehen in eine Liste zu bekommen

Hallo Uwe,

Nein, da gibt es keinen direkten Weg.

Der Unterschied ist das Excel.Workbook die gespeicherte Datei auf der Festplatte liest, hingegen liest Excel.CurrentWorkbook die Instanz im Speicher, gibt aber keine Sheets zurück.
https://learn.microsoft.com/en-us/powerq...ntworkbook

Für Dateien die irgendwo auf einer Platte liegen kann man sich mit einer Parametertabelle und 
=WECHSELN(LINKS(ZELLE("filename";A1);SUCHEN("]";ZELLE("filename";A1))-1);"[";"")
behelfen um den Path zur aktuellen Datei zu bekommen.

Wenn das Ding online gespeichert wurde geht das alles nicht mehr. Ebenso wenn die Datei auf der Platte mit einem Passwort geschützt ist.

Andreas.

[Warkings]

M is for (Data) Monkey by Ken Puls &Miguel Escobar

Am Ende von Chapter 5 Aggregating Excel Worksheets 

Using =Excel.CurrentWorkbook()
The biggest thing to remember about building solutions using the
Excel.CurrentWorkbook() function is that this function reads all the objects in the
current file. Since this affects the calculation chain, you get hit with a recursion
effect, meaning that as the new tables are built, Power Query recognizes them
and reads them as potential content as well.
The implications of this appear at refresh, when the query attempts to load itself,
thereby duplicating the data in the output. When working with this method, it is
important to remember this and guard against it. Strategies to protect against
problems here range from filtering errors on key columns to using naming
standards for both your input and output columns so you can filter out the ones
you don’t need.

Note: Whatever method you choose, make sure to test it through a couple
refreshes before releasing it to production!

Using =Excel.Workbook([Content])
Unlike with Excel.CurrentWorkbook(), using the Excel.Workbook() function
doesn’t cause any recursion issues. The reason is that Excel.Workbook() reads
from the most recently saved copy of an external Excel workbook. This can
obviously have implications if a user is in the process of updating the file but
hasn’t saved it, as you won’t get the most current data that you might expect.
The other key concern when using the Excel.Workbook() function to extract the
contents of a workbook is that it pulls worksheets in addition to ranges and
tables. This makes it very easy to duplicate data if you are not careful, as all
named ranges and tables exist in worksheets. Pay careful attention to filtering the
Kind column to avoid this issue.

Note: Even if there is only one kind of data in the workbook when you build
the solution, it’s not a bad idea to future-proof your solution by forcing a
filter to accept only the desired kind. This will prevent issues when a user
adds a table to a data file that never had one.

It should also be recognized that it is entirely possible to use Excel.Workbook() to
read from the current workbook, thereby exposing the ability to read worksheet
content. (This is useful if you can’t define print ranges or other ranges over your
data.) Remember, however, that the query will read from the most recently saved
copy of the workbook, not the live copy. This has the unfortunate side effect of
still facing the recursion issues from the Excel.CurrentWorkbook() function, but
you may not see them until the next time you open the file.

[Ralf A]

...[Kind] kann ich nur nutzen, wenn ich von außen aus Workbook zugreife via Pfad.

Mit VBA ist das natürlich überhaupt kein Ding.

Wenn das mal kein Irrtum ist.... 
Mit VBA ist es auch nur dann kein Ding, wenn Du sicher weißt, wie die Blätter heißen. Dann ist es aber auch für PQ kein Ding. Dann gibst Du einfach die entsprechenden Parameter ein.
Es gibt allerdings nicht nur Tabellenblätter (Sheet) als Datenbereiche, sondern auch formatierte Tabellen (Table) und Namensbereiche (Defined Name). Und wenn Du nicht weißt, was sich da alles in Deinem Workbook befindet und wie sdas heißt, wie willst Du das herausfinden, wenn Du nicht darauf zugreifst? Das kann auch VBA nicht... 

Wie schon erwähnt, für die aktuelle Datei kannst Du Dir das alles über Excel.CurrentWorkbook() ,
für eine externe Datei über Excel.Workbook(File.Contents("kompletterPfad"), null, true)
und für alle Dateien eines Ordner über  Folder.Files("Ordnerpfadangabe")
anzeigen lassen. Wenn Du also nicht weißt, was in der Datei so an Datenbereichen enthalten ist, kannst Du über die damit gewonnenen Infos dann das auswählen was Du haben möchtest.

Wenn Du aber schon alles über alle Namen und Objektarten weißt, dann kannst Du das selbstvertändlich auch direkt angeben...  

Z. Bsp. so:
Quelle = Excel.CurrentWorkbook()[Content]{0}[Datei]{3}

Alles klar? Oder doch Erläuterung?
Den 2. Datensatz der Spalte Datei vom 1. Datensatz der Spalte Content aller Angaben zum aktuellen Workbook... :)

---

Wenn das Ding online gespeichert wurde geht das alles nicht mehr. Ebenso wenn die Datei auf der Platte mit einem Passwort geschützt ist.

Ich hab ja de meisten meiner Dateien alle auf OneDrive gespeichert. Und da diese Dateien ja auch synchronisiert werden, findet man die im Verzeichnis C:\Users\User\OneDrive. Diese Pfadangabe kann man dann doch als Stammverzeichnis benutzen...

[Egon12]

Hallo Miteinander,

vielen Dank für die Hinweise.
 
@ws-53
mit  Excel.CurrentWorkbook() hatte ich ja festgestellt, dass [Kind] 0 ist. Das funktioniert so leider nicht.
 
@Andreas Killer und Warkings,
das hatte ich nach längerem rumgesuche auch schon vermutet.
 
@Ralf A,
 
VBA ist für mich einfach. 
sinnbefreites Beispiel:

Code:

Sub BlattName()
    Dim wks As Worksheet, tmp$
    For Each wks In ThisWorkbook.Worksheets
        If InStr(1, wks.Name, "nicht", vbTextCompare) = 0 Then tmp = tmp & wks.Name & vbCrLf
    Next
    MsgBox tmp
End Sub

Gruß Uwe

[Ralf A]

Code:

Sub BlattName()
    Dim wks As Worksheet, tmp$
    For Each wks In ThisWorkbook.Worksheets
        If InStr(1, wks.Name, "nicht", vbTextCompare) = 0 Then tmp = tmp & wks.Name & vbCrLf
    Next
    MsgBox tmp
End Sub

...sag ich doch... Du musst Deine Datei kennen. Ich käme im Leben nicht auf die Idee, die Blätter einer Datei daraufhin zu prüfen, ob in deren Namen "nicht" enthalten ist. Es sei denn, ich wüßte, dass da eins mit "nicht" im Namen dabei sein sollte... :)

[ws-53]

Ich hatte geschrieben:

... oder wenn du die Tabellenblätter zumindest als benannte Bereiche definiert hast, kannst du auch: "Excel.CurrentWorkbook()" verwenden.

[Ralf A]

Die Begrifflichkeiten sollten schon stimmen.

ein Tabellenblatt ist für PQ ein Sheet
eine formatierte Tabelle ein Table
und ein benannter Bereich (also ein über einen vergebenen Namen definierter (Teil)-Bereich eines Tabellenblattes) ein Defined Name 

Über Excel.CurrentWorkbook() wird alles angezeigt, was davon vorhanden ist und Daten enthält.

[Egon12]

@ws-53,

ja, via Namensmanager geht das natürlich, dass hätte ich noch dazu schreiben sollen.

Das hatte ich dann so:

Code:

let
    Quelle = Excel.CurrentWorkbook(),
    NurBlatt = Table.SelectRows(Quelle,each Text.Contains(Text.Lower([Name]), "blatt")),
    NurNamen = Table.SelectColumns(NurBlatt, {"Name"}),
    Umbenannt = Table.RenameColumns(NurNamen, {{"Name", "Mitarbeiter"}})
in
    Umbenannt

Da die Sache aber ohne Einbau in den Namensmanager, also einfach neues Blatt erzeugen und fertig sein soll, fand ich nur den Weg via Pfad von außen. Da muss man dann eben vorher den Speicherbutton drücken.

Gruß Uwe