Rohdaten ->Power query -> neue Tabelle, Berechnung

[Arek100]

Hallo Experten,

ich habe eine Excel Tabelle mit Rohdaten damit diese für alle zugänglich sind.

Die Rohdaten werden in einer neuen Excel Tabelle über Power Query aufbereitet (unnötige Spalte entfernt, Formatierung und Umbenennungen durchgeführt) und zugänglich gemacht damit jeder eigene Einstellungen verwenden kann z.B. eigene Filter.

In den Rohdaten sind es Spalten von A bis S, teilweise mit Formeln.

In einer Spalte wird diese Formel verwendet („=HEUTE()-[@[End Datum]]“ Spalte i = End Datum, Spalte j = Formel) also heute minus eingestelltes Datum und als Zahl angezeigt. Das End Datum kann sich in runtergehenden Zellen ändern.

Es können im Laufe der Zeit Zeilen dazu kommen oder auch entfernt werden aber eher neue dazu kommen.

In der neuen Tabelle (Übersicht) landet das Ergebnis nach Entfernung unnötiger Spalten mitten drinnen in der Spalte L.

Leider Ergibt sich jetzt das Problem das die Daten in Spalte L immer den Wert beim letzten abspeichern der neuen Tabelle entsprechen und sich somit nicht ändern wenn die neue Tabelle Tage später aufgemacht wird außer ich öffne die Rohdaten, speichere neu ab und aktualisiere wieder die Daten in der neuen Tabelle.

Es kann gut möglich sein das sich die Rohdaten mehrere Tage/Wochen nicht ändern außer dem Wert in Spalte J. Es wäre wichtig das sich der Wert in der neuen Tabelle in Spalte L ändert also das die Formel aus den Rohdaten beibehalten wird.

Kann man power query so einstellen das die Formel übernommen wird aus Spalte j oder hat vielleicht jemand eine Idee wie ich dieses Problem lösen könnte.

Aktuell denke ich über eine Hilfspalte nach in der neuen Tabelle aber diese würde ja immer überschrieben werden und wie könne ich den Verweis auf die Hilfspalte behalten.

Danke und Gruß

Arek

 

[deineMudda]

Hi!

Daten --> Verbindungen --> Verbindung auswählen --> Eigenschaften --> Optionen bei "Aktualisierungssteuerung" nach deinen Vorstellungen anpassen

 

[Arek100]

Hi,

die Option kenne ich, weil ich diese schon in anderen Excel Dateien verwende und dort es gut Funktioniert da ich die Rohdaten erst im den „zweiten“ Excel Dateien Auswerte/Berechne, leider sehe ich nicht wie sie mir bei diesem Vorhaben helfen könnte. Das Problem ist, dass ich die Datei mit den Rohdaten hier vorher öffnen müsste damit dort die Berechnungen durchgeführt werden und erst dann kann ich mir die Daten holen und das wollte ich vermeiden. Auch wollte ich vermeiden ein zweites Blatt zu haben wo die Rohdaten erst geladen werden, Berechnet und dann in einem zweiten Blatt übersichtlich da gestellt werden wie in den anderen Dateien die ich verwende.
So wie es aussieht, komme ich um ein zweites Blatt für die Berechnungen nicht herum.

Gruß
Arek

 

[deineMudda]

@Arek100 Keine Ahnung was du machst, aber so funktioniert es. Evtl. hast du die falsche Verbindung ausgewählt. Wenn deine Tabelle z. B. Daten heißt, dann must du auch diese Verbindung bearbeiten (also die Verbindung der Tabelle für die Enduser bearbeiten, nicht die Verbindung für die Quelle).

Nachtrag: Haben die User Verbindungen aktiviert bzw. die Rechte dazu? Wenn das nicht der Fall ist, kann es natürlich auch nicht klappen.

 

[Arek100]

Hi, die Verbindung ist ja nicht das Problem. Ich versuche es noch mal zu beschreiben:

In den Rohdaten wird mit heute minus Zelle Datum eine Differenz ausgerechnet Ergebnis sagen wir -12. Wenn ich die Rohdaten jetzt in meine neue Datei übernehme wird in die Zelle -12 übernommen, passt. Das Problem ist jetzt, wenn ich die Datei zwei Tage später aufmache steht in der Zelle immer noch -12.

Da ist halt meine Frage ob ich z.B. die Formel behalten kann die in den Rohdaten steht, wenn ich diese über Power Query hole, weil wenn ich in die Zelle jetzt eine Formel einsetzte, wie in den Rohdaten, um heute – Zelle zu Berechnen, wird durch das aktualisieren a) die Formel überschrieben und b) da ich die Rohdaten in den letzten zwei Tagen nicht angefasst habe steht da wieder -12. Ich müsste jetzt die Rohdaten öffnen, speichern und nach dem Ausführen von aktualisieren in der neue Datei hätte ich erst jetzt in der Zelle -10 stehen.

Theoretisch wüsste sich, wenn ich aktualisieren wähle, die Datei mit den Rohdaten öffnen im Hintergrund und wieder selbständig speichern und damit in den Rohdaten die Berechnung durchführen.

Ich weiss das es dafür makro's gibt aber ich wollte es wissen ob es auch ohne geht.

Gruß
Arek

 

[deineMudda]

@Arek100 OK, ich habe verstanden was du meinst, aber das ist der falsche Ansatz. Wenn die Quell-Datei nicht mindestens jeden Tag aktualisiert wird, funktioniert das so nicht. Excel speichert bei jedem Speichern das Ergebnis der Formel als Wert ab. Warum berechnest du die Differenz in Tagen nicht einfach in der Datei der Endanwender?

Über PowerQuery geht es auch:
Spalte hinzufügen --> benutzerdefinierte Spalte --> Feld mit Datum auswählen und Formel wie folgt bearbeiten:

= Table.AddColumn(#"Changed Type1", "iDiffDays", each [Datum1]-Date.From(DateTime.LocalNow()))

Anschließend die neue Spalte mit Transformieren --> Datentyp --> Dezimalzahl in eine Dezimalzahl umwandeln.
Der entscheidente Part ist dieser hier:

Date.From(DateTime.LocalNow())

Date.From wandelt den Zeitstempel, den DateTime.LocalNow() liefert in ein Datum um.