May 18, 2020
Dinamični koledar v Power Query z M funkcijo
V članku je uporabljen Power BI Desktop verzije 2.76.5678.782 (januar 2020).
Koledar je najpomembnejša tabela v podatkovnem modelu Power BI. Ogledali smo si že Koledar v Power BI in Dinamični koledar v Power Query. Korake, po katerih smo postopali v zadnjem članku, lahko zapišemo v eno samo funkcijo, ki jo uporabimo vsakič, ko moramo izdelati dinamični koledar v našem podatkovnem modelu.
Jezik M
V zadnjem članku smo si ogledali izdelavo dinamičnega koledarja v urejevalniku Power Query. Vsi koraki, s katero smo transformirali podatke v urejevalniku Power Query, so se izpisali v okno Applied Steps. Vsak korak je zapisan v jeziku M, ki si ga lahko ogledamo v vnosni vrstici za formulo. Korake zato lahko med seboj združujemo, kar nam prihrani čas. Koledarsko tabelo bomo potrebovali v vsakem podatkovnem modelu, zato je priročno, da v ta namen uporabimo že spisano funkcijo.
Izdelava dinamičnega koledarja
Izdelave dinamičnega koledarja se lotimo podobno kot v prejšnjem članku. Povežemo se na podatke in jih odpremo v urejevalniku Power Query. V naših podatkih se tokrat datumi nahajajo v več stolpcih in tabelah. Zanimala nas bosta le stolpca Order Date in Payment Date v tabeli DateNTime.
Podatkovna tabela z dvema datumskima stolpcema.
Pri izdelavi koledarja moramo biti pozorni na rojstne datume. V našem modelu se eden od stolpcev imenuje RD in vsebuje rojstne datume. Rojstnih datumov v koledarju običajno ne želimo, saj nam močno povečajo velikost koledarske tabele. S poslovnimi rezultati običajno niso povezani, zato jih lahko brez škode spustimo pri izdelavi koledarske tabele.
Stolpca Order Date in Payment Date želimo tudi tokrat zbrati v en stolpec. Z desnim klikom na tabelo DateNTime ustvarimo referenco s klikom na Reference. Referenco poimenujemo Calendar. Z desnim klikom na stolpec Order Date in izbiro Remove Other Columns odstranimo druge stolpce. Nato za vse datume poračunamo začetek leta z desnim klikom na stolpec in izbiro Transform > Year > StartOfYear. Odstranimo še dvojnike z desnim klikom in izbiro Remove Duplicates. Stolpec še preimenujemo v Date. Na enak način ustvarimo in uredimo še referenco za stolpec Payment Date v isti tabeli DateNTime. Referenci nato združimo: v tabeli Calendar kliknemo Append Queries in izberemo drugo tabelo, v našem primeru poimenovano Calendar1. Še enkrat ostranimo duplikate. Dobili smo začetke vseh let datumov iz prvotnih stolpcev. Ime stolpca še preimenujemo v Date.
Oba relevantna datuma združena v en datumski stolpec.
Zdaj lahko ustvarimo novo poizvedbo s funkcijo, ki bo za pripravljene datume izdelala tabelo vseh zaporednih datumov. V oknu Queries z desnim klikom izberemo New Query > Blank Query. Nato izberemo Advanced Editor in vanj prilepimo funkcijo:
| (TableName as table,ColumnName as text)=>
let Source = TableName, #”Removed Other Columns” = Table.SelectColumns(Source,{ColumnName}), #”Renamed Columns” = Table.RenameColumns(#”Removed Other Columns”,{{ColumnName, “Date”}}), #”Calculated Start of Year” = Table.TransformColumns(#”Renamed Columns”,{{“Date”, Date.StartOfYear, type date}}), #”Removed Duplicates” = Table.Distinct(#”Calculated Start of Year”), #”Duplicated Column” = Table.DuplicateColumn(#”Removed Duplicates”, “Date”, “Date – Copy”), #”Renamed Columns1″ = Table.RenameColumns(#”Duplicated Column”,{{“Date – Copy”, “Date2″}}), #”Calculated End of Year” = Table.TransformColumns(#”Renamed Columns1″,{{“Date2″, Date.EndOfYear, type date}}), Base = Table.TransformColumnTypes(#”Calculated End of Year”,{{“Date”, Int64.Type}, {“Date2″, Int64.Type}}), MinDate = List.Min(Base[Date]), MaxDate = List.Max(Base[Date2]), Custom2 = {MinDate..MaxDate}, #”Converted to Table” = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #”Renamed Columns2″ = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Date”}}), #”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns2″,{{“Date”, type date}}), #”Inserted Month” = Table.AddColumn(#”Changed Type”, “Month No”, each Date.Month([Date]), Int64.Type), #”Inserted Year” = Table.AddColumn(#”Inserted Month”, “Year”, each Date.Year([Date]), Int64.Type), #”Inserted Month Name” = Table.AddColumn(#”Inserted Year”, “Month”, each Text.Upper(Text.Start(Date.MonthName([Date]),3)), type text), #”Inserted Quarter” = Table.AddColumn(#”Inserted Month Name”, “Quarter”, each “Q”&Text.From(Date.QuarterOfYear([Date])), type text), #”Added Custom” = Table.AddColumn(#”Inserted Quarter”, “YM”, each if [Month No]>9 then “-” & Text.From([Month No]) else “-0″ & Text.From([Month No])), #”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Year Month”, each Text.From([Year])&[YM]), #”Removed Columns” = Table.RemoveColumns(#”Added Custom1″,{“YM”}), #”Changed Type1″ = Table.TransformColumnTypes(#”Removed Columns”,{{“Year Month”, type text}}), #”Inserted Day of Week” = Table.AddColumn(#”Changed Type1″, “Weekday No”, each Date.DayOfWeek([Date]), Int64.Type), #”Inserted Day Name” = Table.AddColumn(#”Inserted Day of Week”, “Weekday”, each Text.Upper(Text.Start(Date.DayOfWeekName([Date]),3)), type text), #”Inserted Week of Year” = Table.AddColumn(#”Inserted Day Name”, “Week of Year”, each Date.WeekOfYear([Date]), Int64.Type), #”Added Custom2″ = Table.AddColumn(#”Inserted Week of Year”, “Custom”, each if [Week of Year]>9 then Text.From([Week of Year]) else “0”&Text.From([Week of Year])), #”Added Custom3″ = Table.AddColumn(#”Added Custom2″, “Week”, each “W”&[Custom]), #”Removed Columns1″ = Table.RemoveColumns(#”Added Custom3”,{“Week of Year”, “Custom”}) in #”Removed Columns1″ |
Funkcija v jeziku M, ki bo za izbran datumski stolpec izdelala tabelo zaporednih datumov. V našem primeru bo za datumski stolpec, ki vsebuje prve dneve med letoma 2000 in 2020, generirala tabelo vseh zaporednih datumov med 1.1.2000 in 31.12.2020. Poleg datumov bodo v tabeli še vsi njegovi atributi kot so dan, teden, kvartal in leto.
Vnos potrdimo z Done. Funkcija, ki smo jo ustvarili, ima zdaj dve vnosni polji za izbiro parametrov:
Ustvarjena M funkcija nam v izbiro ponudi vnosne parametre.
V polju TableName izberemo tabelo Calendar, v polje ColumnName pa vpišemo ime stolpca Date. Nato kliknemo na Invoke. Funkcija ustvari novo poizvedbo oz. tabelo z imenom Invoked Function, ki vsebuje vse zaporedne datume od začetka najmanjšega leta, do konca največjega. Poleg datumov tabela vsebuje še vse atribute kot so dan, teden, mesec, kvartal in leto.
Končana koledarska tabela.
Podatki so zdaj pripravljeni. Preden jih naložimo v model izklopimo nalaganje tabel, ki nas ne zanimajo. V našem primeru sta to tabeli Calendar in Calendar1. Kliknemo na vsako od tabel z desnim klikom in odstranimo kljukico pri Enable load. Tabeli sta zdaj zapisani poševno, kar nakazuje, da se v model ne bosta naložili.
Onemogočimo nalaganje tabel, ki nas ne zanimajo.
Na koncu še potrdimo uvoz podatkov z izbiro Close & Apply.
Zakaj je funkcija M boljša izbira kot CALENDARAUTO()
Funkcija CALENDARAUTO() prebere vse datume v naših podatkih – če imamo med podatki rojstne datume, ki nas običajno ne zanimajo, bomo po nepotrebnem dobili zelo veliko tabelo, ki bo upočasnjevala delovanje našega modela. To je razlog, zakaj je izdelava dinamičnega koledarja s funkcijo M boljša izbira.
Drugi razlog, ki gre v prid M fukciji je ta, da poleg vseh datumov funkcija CALENDARAUTO() zazna tudi čas, in ga tretira kot datum. Vsak časovni podatek bo tretiran kot datum 0.1.1900, saj je to prvi dan, od katerega Power BI šteje datume. Pri izdelavi koledarja s funkcijo CALENDARAUTO() bomo tako dobili tabelo od 1.1.1899 naprej (saj 0.1.1900 ne obstaja). Več o datumih in časih si bomo ogledali v članku Dinamični koledar v Power BI z DAX funkcijo CALENDARAUTO(). Vsekakor pa nam metoda, opisana v tem članku, daje največji nadzor nad tem, kakšna naj bo naša koledarska tabela.