Kolmas pähkinä, sama ongelma! Lupaan, nyt alkaa olla kielet käyty, ellei sitten mennä Python/R tai ehkä ADF:n dataflow:hun? Mutta tänään siis Power Queryllä tuttu kumulatiivinen summa ongelma. Lisätään hieman vaikeutta, ratkaisun tulee olla yksi kysely. Power Queryssä ei siis saa näkyä uusia kyselyitä. Steppejä tietty voi olla vaikka kuinka monta.
Kumulatiivinen summa Power Query kielellä
Pähkinänä on siis ratkaista miten Power Queryllä saadaan vastattua kysymykseen: “Myynti kunakin vuonna kyseisen kuukauden loppuun”. Summa siis kasvaa joka kuukausi, kunnes taas tammikuussa lähtee alusta.
Lähtödata on:
Ja kun olet tehnyt taikuudet, on tulosjoukko seuraavanlainen:
Näet että summa kasvaa aina vuoden ensimmäisestä kuukaudesta eteenpäin ja on isoimmillaan joulukuussa, jonka jälkeen se alkaa alusta.
Tehtävän suorittamista varten tarvitset oheisen Power BI Desktop tiedoston. Siinä on sinulle kysely “CumulativeSum” valmiina, jota voit muokata. Jos Power BI Desktop kysyy datalähteen oikeuksia, ovat ne vanhat tutut:
- Palvelin: qsql.database.windows.net
- Käyttäjätunnus (sql autentikaatio): joulu
- Salasana: kalenteri123!
- Tietokannan nimi: AdventureWorksLT (huomaa että käyttäjällä ei ole oikeutta muihin tietokantoihin, joten sinun tulee merkitä tietokanta jo yhdistämisvaiheessa).
Palauta tuttuun tyyliin Microsoft Forms -lomakkeella tänne.
Pähkinän #2 vastaus
Palautetuissa vastauksissa oli kahta päätyyppiä. Joko väliaikaistaulu with-rakenteella ja sitten tarvittava määrä kyselyitä siihen, tai sitten ehkä se SQL Server mäisempi ikkunafunktioiden käyttö. Hauskin vastaus oli kysely, jossa kommenteissa luki “Lopputulos on oikein, mutta poiminta meni spagetin puolelle!” Vastauksia tuli 14 kpl.
Minun esimerkkivastaukseni hyväksikäyttäisi ikkunointifunktioita, koska ne on erikseen optimoitu SQL Serverissä. Voisi siis olettaa niiden olevan hieman tehokkaampi tapa suurella tietojoukolla.
select [CalendarYear],[MonthNumberOfYear],
convert(decimal,SUM(SalesAmount) OVER (PARTITION BY [CalendarYear] ORDER BY [MonthNumberOfYear]ROWS UNBOUNDED PRECEDING)) as CumulativeSum
from (select [CalendarYear],[MonthNumberOfYear],SUM(SalesAmount) as SalesAmount from [dbo].[FactInternetSales] as f
inner join
[dbo].[DimDate] as d ON f.OrderDateKey=d.DateKey
group by [CalendarYear],[MonthNumberOfYear]) as t