Power Query - alte Werte behalten

Excelbitionist · 2023-03-14T13:51:35+00:00

Liebe Forist:innen, ich habe schon öfter hier nachgeschaut und gute Antworten auf Probleme gefunden, und wollte hiermit gleich einmal Danke sagen! Heute habe ich ein Thema, welches ich zumindest so nicht gefunden habe und hoffe auf einen Hinweis, wie das zu beheben ist: Ich habe mit PowerQuery eine Abfrage auf eine Seite mit Zinssätzen (https://www.newyorkfed.org/markets/reference-rates/sofr-averages-and-index). Dort werden aber immer nur die letzten 25 Werte abfragebereit zur Verfügung gestellt. Ich habe nun im Wesentlichen zwei Probleme: 1. Da ich die Daten bspw. von Beginn des Jahres an in einer Tabelle haben möchte, könnte ich diese auf der o.g. Seite im unteren Bereich eingrenzen und abfragen und in Excel exportieren. Dann müsste ich das nur noch mit der (dynamischen) Abfrage verknüpfen. Das würde ich vielleicht sogar noch hinbekommen, aber damit kommen wir zu meinem weitaus größeren, zweiten Problem: 2. Wenn ich morgen abfrage, ist der erste Eintrag in der Liste (der nunmehr 26igletzte Eintrag) nicht mehr abrufbar. Auf die Art und Weise kann ich keine Historie aufbauen. Meine Idee war, dass ich eine Kombination aus Werten (Historische Daten) habe, die bei jeder Abfrage um die Differenz (also die bislang noch nicht in der Tabelle enthaltenen Werte) ergänzt wird. Ich habe das mit Append versucht, bin aber leider gescheitert. Hat jemand eine Idee? Vielen Dank im Voraus und viele Grüße Excelbitionist

Hi
Wenn du mal auf XML drückst sieht du einen Link, welcher als Parameter startPosition=0 enthält. Wenn du den Parameter änderst, kannst du auch auf ältere Daten zugreifen.
z.B. https://www.youtube.com/watch?v=QHX_bb9OGe4
Somit musst du die alten Daten nicht speichern (wäre blöd, wenn du mal im Urlaub bist). Du holst dir einfach so viel Historie wie du gerne möchtest. Der Wert 500 in der Hauptabfrage lässt sich bei Bedarf noch erhöhen.
GetData

(index as number) as table =>
let
Quelle = Xml.Tables(Web.Contents("https://markets.newyorkfed.org/read?productCode=50&eventCodes=525&limit=25&startPosition=" & Text.From(index) & "&sort=postDt:-1&format=xml")),
    Table0 = Quelle{0}[Table],
    Table1 = Table0{0}[Table],
    #"Geänderter Typ" = Table.TransformColumnTypes(Table1,{{"effectiveDate", type date}, {"type", type text}, {"average30day", type number}, {"average90day", type number}, {"average180day", type number}, {"index", type number}})
in
    #"Geänderter Typ"

Hauptabfrage

let
    Quelle = List.Generate(() => 0, each _  500, each _ + 25),
    #"In Tabelle konvertiert" = Table.FromList(Quelle, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Aufgerufene benutzerdefinierte Funktion" = Table.AddColumn(#"In Tabelle konvertiert", "GetData", each GetData([Column1])),
    #"Erweiterte GetData" = Table.ExpandTableColumn(#"Aufgerufene benutzerdefinierte Funktion", "GetData", {"effectiveDate", "type", "average30day", "average90day", "average180day", "index", "revisionIndicator"}, {"effectiveDate", "type", "average30day", "average90day", "average180day", "index", "revisionIndicator"})
in
    #"Erweiterte GetData"

cu
Chris

Lieber Chris,
lieber Rudi,
vielen Dank für die schnelle Hilfe und sorry für die späte Antwort meinerseits.
@Chris:
Ich habe überall gesucht, aber leider nicht gefunden wo ich auf XML klicken soll... Ich stehe glaube ich auf dem Schlauch, ich habe die Datei im Format xlsx erstellt und nicht mit Makros.
Oder meinst Du den erweiterten Editor (analog zum von Dir verlinkten Video)? In dem Fall sind leider keine Parameter zu sehen, lediglich die Anpassungen, die ich vorgenommen habe:
let
Quelle = Web.Page(Web.Contents("https://www.newyorkfed.org/markets/reference-rates/sofr-averages-and-index")),
Data0 = Quelle{0}[Data],
#"Sortierte Zeilen" = Table.Sort(Data0,{{"DATE", Order.Ascending}}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Sortierte Zeilen",{{"DATE", type text}, {"30-DAY AVERAGE (%)", type number}, {"90-DAY AVERAGE (%)", type number}, {"180-DAY AVERAGE (%)", type number}, {"INDEX", type number}}),
Divisionsspalte = Table.TransformColumns(#"Geänderter Typ", {{"30-DAY AVERAGE (%)", each _ / 100000, type number}}),
Divisionsspalte1 = Table.TransformColumns(Divisionsspalte, {{"90-DAY AVERAGE (%)", each _ / 100000, type number}}),
Divisionsspalte2 = Table.TransformColumns(Divisionsspalte1, {{"180-DAY AVERAGE (%)", each _ / 100000, type number}}),
Divisionsspalte3 = Table.TransformColumns(Divisionsspalte2, {{"INDEX", each _ / 100000000, type number}}),
#"Geänderter Typ mit Gebietsschema" = Table.TransformColumnTypes(Divisionsspalte3, {{"DATE", type date}}, "en-US"),
#"Angefügte Abfrage" = Table.Combine({#"Geänderter Typ mit Gebietsschema", #"Geänderter Typ mit Gebietsschema"}),
#"Entfernte Duplikate" = Table.Distinct(#"Angefügte Abfrage", {"DATE"})
in
#"Entfernte Duplikate"
Ich hatte lediglich den von mir genannten Link als Quelle (Daten->neue Abfrage ->andere Quellen->aus dem Web angelegt und damit nur den oberen Teil auf der Zielseite abfragen können, auf den unteren Part bei der FED-Seite (auf der man ein Datum eingeben kann) kam ich nicht drauf).
@Rudi:
Ich bin leider nicht im Homeoffice und aufgrund der Firewall-Einstellungen (proxy meldet '407') kann ich den Abruf frühestens heute Abend wieder zuhause testen.
Ich bräuchte aber dennoch ja irgendwie die Daten von einem bestimmten Startzeitpunkt aus (unabh. davon, ob für zukünftige Abfragen die alten (und nicht mehr in der neuen Abfrage enthaltenen) Daten erhalten bleiben.
Vielen Dank und viele Grüße
Joe

Power Query - alte Werte behalten

Forumthread: Power Query - alte Werte behalten

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Infobox / Tutorial