Generate Calendar Table from Min/Max Seed

user_19042379 0
Viewed 71

I'm trying to create a calendar from YYYYMM (Integer) value in my SQL table. Here is the first step - getting the start value = "01 jan 2019" in a Date format. The code below returns an error with a description that it "cannot convert a value of type Record to type Text".

let
mySource=
   Sql.Database("sqlServer"
      ,"DWH"
      ,[Query=
         "SELECT 
               MIN(mdate) as mdate 
            FROM BD_plan_AC 
          UNION 
          SELECT 
            MAX(mdate) as mdate 
         FROM myTable"
      ] 
 )-- here I get the mdate range within I want to build my calendar.
  -- from the first day of mdate to the end of the last mdate. 

,startDateAsNum=Record.Field(Table.First (mySource),"mdate")*100+1 
 // Table.First (mySource),"mdate")=201901
 // startDateAsNum=20190101
,startDateAsText=Text.From(startDateAsNum)
,startDateAsDate=Date.FromText(startDateAsText,[Format="yyyyMMdd"])
in
startDateAsDate

Many thanks in advance.

The idea is to get the mdate range and build a calendar from the first day of min to the last day of max, so, all dates from the range are in.

mySource = -- dates are not constant and comes from the query.

mdate
201901
202212

This, what I want to get finally - a calendar with a Date() data type. (Dates are as dd.mm.yyyy)

Date
01.01.2019
02.01.2019
....
31.12.2022
powerquery
3 Answers

try this to get the data from SQL in Format yyyymmdd then maybe you have to convert anything...

SELECT 
              format(cast(MIN(mdate) ,datetime),"YYYYMM") as mdate 
            FROM BD_plan_AC 
          UNION 
          SELECT 
            format(cast(MAX(mdate) ,datetime),"YYYYMM")  as mdate 
         FROM myTable
user_52602650

Why do you need to do anything?

01 jan 2019 converts perfectly to a date with Date.From() in powerquery

user_92642300

Given your input data, you can generate a calendar as follows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDQwVIrVATGNjAyNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [mdate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"mdate", type text}}),
    start = #date( Number.From( Text.Range( #"Changed Type"[mdate]{0},0,4 )) ,Number.From( Text.Range( #"Changed Type"[mdate]{0},4,2 )),1 ),
    end = #date( Number.From( Text.Range( #"Changed Type"[mdate]{1},0,4 )) ,Number.From( Text.Range( #"Changed Type"[mdate]{1},4,2 )),31 ),
    cal = { Number.From( start ) .. Number.From( end ) },
    #"Converted to Table" = Table.FromList(cal, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
    #"Changed Type1"
user_183450370
Related