Spargerea unei interogari in mai multe interogari cu Functii personalizate in PowerQuery

Prezentare generală

Uneori, devine necesar să împărțim o interogare în mai multe părți, să procesăm fiecare segment individual, să recuperăm datele și apoi să combinăm rezultatele într-un singur tabel folosind operațiuni de Append sau Union.

Exemplul 1: Web Scraping

Problema de rezolvat:

Imaginați-vă acest scenariu: trebuie să obținem cursurile de schimb EUR pentru ultimele trei luni și să construim un tabel cu date istorice.

Această informație este disponibilă la următorul URL:
https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html

Provocarea constă în faptul că datele sunt disponibile doar la granularitate zilnică și trebuie descărcate zilnic, deoarece nu există un tabel consolidat furnizat.

Deoarece datele sunt găzduite pe un site web, trebuie să folosim conectorul Web. Pentru a permite automatizarea în Power Query, URL-ul trebuie să aibă o structură consecventă.

Să selectăm o dată pe pagina web și să alegem exportul PDF (deoarece exportul CSV nu va expune ușor URL-ul).

Așa cum putem observa, PDF-ul are următoarea structură de URL:

https://www.ecb.europa.eu/stats/exchange/eurofxref/shared/pdf/2024/11/20241114.pdf

Să examinăm structura URL-ului:

Deoarece componentele Year/Month/YearMonthDay din URL corespund datei 14 noiembrie 2024, exportul preia date pentru acea zi specifică. Dacă modificăm componenta YearMonthDay la 20241113, exportul va furniza date pentru 13 noiembrie 2024.

Soluția

Pentru a exporta datele pentru octombrie și noiembrie 2024, trebuie să urmăm acești pași:

Crearea unui tabel cu toate datele de la 1 octombrie la 30 noiembrie.
Crearea unei interogări pentru a citi datele de pe web.
Transformarea interogării într-o funcție personalizată.
Apelarea funcției pentru fiecare dată din pasul 1 pentru a prelua fișierele PDF pentru fiecare zi.
Combinarea tuturor tabelelor din pasul 4 într-un singur tabel consolidat.

Descriere detaliată

1. Crearea unui tabel cu toate datele din 1 octombrie – 30 noiembrie

În Power Query, creați o interogare goală și…

În bara de formule, introduceți:

= {20241001..20241130}

Această sintaxă {1..n} va crea o listă cu toate numerele de la 1 la n.

Convertiți lista în tabel
Schimbați tipul datelor în text (deoarece în Power Query nu putem concatena numere cu text)
Adăugați coloane pentru an și lună

let
    Query1 = {20241001..20241130},
    #"Converted to Table" = Table.FromList(Query1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start([Column1], 4), type text),
    #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle([Column1], 4, 2), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Range",{{"First Characters", "Year"}, {"Text Range", "Month"}, {"Column1", "Datenum"}})
in
    #"Renamed Columns"

Pasul 2: Utilizarea conectorului Web și furnizarea URL-ului pentru exportul PDF

Accesați Power Query
Selectați New Source > Web
Introduceți URL-ul pentru exportul PDF, înlocuind valorile an, lună și dată dinamic

Selectati tabelul in navigatorul de previsualizare si selectati OK

Optional, redenumiti coloanele

Pasul 3: Transformarea interogării într-o funcție personalizată

Accesați interogarea creată la Pasul 2
Deschideți Advanced Editor (clic dreapta pe interogare → Advanced Editor)
Modificați codul pentru a accepta parametri dinamici

Acesta este codul M pe care trebuie sa il modificam

let
    Source = Pdf.Tables(Web.Contents("https://www.ecb.europa.eu/stats/exchange/eurofxref/shared/pdf/2024/11/20241114.pdf"), [Implementation="1.3"]),
    Table001 = Source{[Id="Table001"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table001,{{"Column1", type text}, {"Column2", type text}, {"Column3", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Currency ISO"}, {"Column2", "Currency Name"}, {"Column3", "Rate"}})
in
    #"Renamed Columns"

Pentru a crea o funcție personalizată M, trebuie să declarăm parametrii funcției, așa cum este prezentat mai jos:

(year as text, month as text, numdate as text) =>

Apoi, injectăm acești parametri în URL-ul site-ului care găzduiește fișierele PDF (verificați linia Source).

Iată codul complet M cu parametrii integrați:

(year as text, month as text, numdate as text) =>
let
    Source = Pdf.Tables(Web.Contents("https://www.ecb.europa.eu/stats/exchange/eurofxref/shared/pdf/"&year&"/"&month&"/"&numdate&".pdf"), [Implementation="1.3"]),
    Table001 = Source{[Id="Table001"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table001,{{"Column1", type text}, {"Column2", type text}, {"Column3", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Currency ISO"}, {"Column2", "Currency Name"}, {"Column3", "Currency Rate"}})
in
    #"Renamed Columns"

După salvarea acesteia cu un nume semnificativ, veți avea o interogare personalizată funcțională, care poate fi reutilizată pentru a prelua cursurile de schimb pentru diferite date, prin furnizarea parametrilor year, month și numdate.

4. Apelați funcția pentru fiecare dată din Pasul 1 pentru a prelua fișierele PDF pentru fiecare zi.

În interogarea creată la Pasul 1, navigați la „Add Column” → „Invoke Custom Function”.

Furnizați funcția personalizată creată la pasul 3 și coloanele corespunzătoare din Query1 pentru fiecare parametru al funcției.

Această operațiune va scana pentru fiecare rând valorile din tabelul creat la pasul 1 și va apela funcția creată la pasul 3 (pentru fiecare rând).

Putem vedea unele erori: în acest caz, deoarece site-ul Băncii Centrale Europene nu furnizează date în weekenduri.

Putem remedia acest lucru simplu prin eliminarea rândurilor cu erori (acest lucru poate fi gestionat mai eficient înainte de apelarea funcției, dar nu este în scopul acestui tutorial).

5. Combinarea tuturor tabelelor din pasul 4 într-un singur tabel

Selectați pur și simplu butonul Combine

Și selectați coloanele pe care doriți să le extindeți.

Apăsați OK, iar apoi veți avea un tabel cu cursurile valutare pentru toate datele specificate la Pasul 1.

Exemplul 2: Împărțirea unei interogări de bază de date în interogări paralele

Executați interogări paralele asupra unei baze de date prin divizarea unei interogări mari în interogări mai mici, depășind limitările sursei privind numărul de rânduri returnate per interogare.

În acest scenariu:

Definiți o logică de împărțire bazată pe o coloană specifică.
Apelați o funcție pentru fiecare valoare unică din împărțire.
După ce toate interogările sunt executate, combinați rezultatele într-un set de date unificat.

1. Crearea unui tabel cu toate combinațiile de valori pentru împărțire

În acest exemplu, vom împărți după OrderDateKey, care are formatul YYYYMMDD (exemplu: 20070506).

Aceasta este puțin mai complicată:

Trebuie să creăm o listă de valori valide pentru lună în format YYYYMMDD (ex.: 200701, 200702 etc.)
Pentru intervalul 20070501 – 20070630, trebuie să ne asigurăm că includem doar luni valide (evitând valori inexistente, cum ar fi 20070535).

Cod M cu explicații:

let
    // Define the start and end dates
    StartDate = #date(2007, 5, 1),
    EndDate = #date(2007, 6, 30),

    // Generate a list of dates from the start to the end date
    DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),

    // Convert the list of dates to a table
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),

    // Format the dates as YYYYMMDD
    FormattedDateColumn = Table.AddColumn(DateTable, "FormattedDate", each Text.From(Date.Year([Date])) 
        & Text.PadStart(Text.From(Date.Month([Date])), 2, "0") 
        & Text.PadStart(Text.From(Date.Day([Date])), 2, "0"), type text),

    // Change the format of the "FormattedDate" column to a numeric type
    FinalTable = Table.TransformColumnTypes(FormattedDateColumn, {{"FormattedDate", Int64.Type}})
    
in
    FinalTable

2. Conectarea la baza de date SQL

Conectați-vă la baza de date SQL.
Aplicați un filtru pe coloana după care doriți să împărțiți încărcarea (OrderDateKey în acest exemplu).
Filtrarea va genera automat codul M, pe care îl vom reutiliza, evitând scrierea manuală.

Accesați Advanced Editor și editați interogarea astfel:
a). Înainte de comanda let, introduceți numele parametrului funcției, urmat de operatorul =>.
b). În pasul de filtrare, înlocuiți valoarea predicatului hardcodat cu parametrul funcției definit la pasul a).

Iată codul M revizuit:

(YearMonthKey_parameter) =>

let
    Source = Sql.Databases("eox2getju5kenbd7swht2r47ji-qzipfkf5lxiupd5g5a4xmyn4vi.datawarehouse.fabric.microsoft.com"),
    ContosoWiki = Source{[Name="ContosoWiki"]}[Data],
    dbo_Sales = ContosoWiki{[Schema="dbo",Item="Sales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_Sales, each ([OrderDateKey] = 20070505))
in
    #"Filtered Rows"

Apelați funcția creată la pasul 3 în tabelul creat la pasul 1.

Expandati si incarcati tabelul resultat

Power BI Garage