none
Problem with Auto-Generated Calender Table (Cannot Convert [Table Name] to type Table RRS feed

  • Question

  • Hi All,

    I have been using the below code for several months now. It originally came from "M is for Data Monkey" book (Love that book!) and I just copy / past it to my new workbook to generate a calendar table.  All of a sudden, I am getting this odd error:

    The only thing I changed was to add variables in the code to capture the date filed and query (table) name so I don't have to keep editing all the names in the m code.  Thought it would be easier to change them in only one place at the beginning of the code.  Here's the code.  The error happens on the "ChangedType" step.

    //Instructions: Create a blank Query.  Copy / Paste this code into the Advanced Editor window.  Be sure to replace "<fldName>" and ","tbbName" variables below to correct names.
    //Query Name..: tblCalendar (Be sure to name it before close and load...)
    //Author......: John Thomas (Idea from "M is for Data Monkey"!)
    //Date........: March, 2017
    //Purpose.....: Create a Table with a record for every day between the Min and Max dates in your data set.  
    //		Min date starts at the first of the month containing data.  Example 1/24/17 min date in data would generate a calendar table starting at 1/1/2017.
    //		Max date ends at the last date containing data + one month. Example 2/28/17 max date in data would generate a calendar table ending at 3/31/17.
    
    let
        fldName = "GLDate", //RENAME TO THE DATE FILED IN YOUR DATA SET.  Should be the one used for the relationship to the fact table!!!
        tblName = "tblPDPmts", //RENAME TO THE TABLE (QUERY) NAME IN YOUR DATA SET!!!
    
        ChangedType = Table.TransformColumnTypes(tblName,{{fldName, type date}}),                               
        MaxDate = Date.EndOfMonth(Date.AddMonths(Record.Field(Table.Max(ChangedType, fldName), fldName), 1)),   
        MinDate = Date.StartOfMonth(Record.Field(Table.Min(ChangedType, fldName), fldName)),                    
        DaysElapsed = Number.From(MaxDate-MinDate),
        DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)),
        RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
        ChangeType_DateToDateType = Table.TransformColumnTypes(RawDatesTable,{{"Date", type date}}),
        InsertedYear = Table.AddColumn(ChangeType_DateToDateType, "Year", each Date.Year([Date]), type number),
        InsertedMonth = Table.AddColumn(InsertedYear, "MonthNo", each Date.Month([Date]), type number),
        InsertedDay = Table.AddColumn(InsertedMonth, "DayNo", each Date.Day([Date]), type number),
        InsertedMonthName = Table.AddColumn(InsertedDay, "MonthText", each Date.MonthName([Date]), type text),
        InsertedDayOfWeek = Table.AddColumn(InsertedMonthName, "DayOfWeek", each Date.DayOfWeek([Date]), type number),
        InsertedQuarterNo = Table.AddColumn(InsertedDayOfWeek, "QuarterNo", each Date.QuarterOfYear([Date]), type number),
        InsertedQuarterText = Table.AddColumn(InsertedQuarterNo, "QuarterText", each Number.ToText([Year]) & "-Q" & Number.ToText([QuarterNo])),
        InsertedEndOfMonth = Table.AddColumn(InsertedQuarterText, "EndOfMonth", each Date.EndOfMonth([Date]), type date),
        AddedLastSaturday = Table.AddColumn(InsertedEndOfMonth, "LastSaturday", each Date.AddDays([EndOfMonth],-1*Date.DayOfWeek([EndOfMonth],Day.Saturday))),
        AddedEOnextM = Table.AddColumn(AddedLastSaturday, "EOnextM", each Date.EndOfMonth(Date.AddMonths([EndOfMonth],1))),
        AddedNextLastSaturday = Table.AddColumn(AddedEOnextM, "NextLastSaturday", each Date.AddDays([EOnextM],-1*Date.DayOfWeek([EOnextM],Day.Saturday))),
        AddedMonthClose = Table.AddColumn(AddedNextLastSaturday, "MonthClose", each if [Date] <= [LastSaturday] then [LastSaturday] else [NextLastSaturday], type date),
        RemovedColumns = Table.RemoveColumns(AddedMonthClose,{"LastSaturday", "EOnextM", "NextLastSaturday"}),
        InsertedWeekEndDate = Table.AddColumn(RemovedColumns, "WeekEnd", each Date.AddDays([Date],6-[DayOfWeek])),
        AddedDDate = Table.AddColumn(InsertedWeekEndDate, "DDate", each Date.AddDays([WeekEnd],1)),  //Note: P6 Data Date starts at midnight, so the data date is 1 day after the Week Ending date.
        InsertedWeekOfMonth = Table.AddColumn(AddedDDate, "WeekOfMonth", each Date.WeekOfMonth([WeekEnd]), type number),
        InsertedDayName = Table.AddColumn(InsertedWeekOfMonth, "DayName", each Text.Start(Date.DayOfWeekName([Date]),3), type text),
        ChangeType_AllFields = Table.TransformColumnTypes(InsertedDayName,{{"Year", Int64.Type}, {"MonthNo", Int64.Type}, {"DayNo", Int64.Type}, {"DayOfWeek", Int64.Type}, {"QuarterNo", Int64.Type}, {"WeekOfMonth", Int64.Type}, {"WeekEnd", Date.Type}, {"QuarterText", Text.Type}, {"DDate", Date.Type}}),
        ReorderColumns = Table.ReorderColumns(ChangeType_AllFields,{"Date", "Year", "QuarterNo", "QuarterText", "MonthNo", "MonthText", "DayNo", "DayOfWeek", "DayName", "MonthClose", "EndOfMonth", "WeekEnd", "DDate", "WeekOfMonth"})
    in
        ReorderColumns

    The table (Query) does exist in the workbook.  Here's the screenshot of the loaded table.

    I am sure this is something silly I am overlooking...

    Thanks for the review.


    John Thomas

    Monday, March 13, 2017 2:39 PM

Answers

  • The first argument of Table.TransformColumnType must be a table, not a string.

    Probably if you remove the quotes around "tblPDPmts", you'll be fine, so:

    tblName = tblPDPmts,

    • Marked as answer by jbt_PwrPvt Monday, March 13, 2017 3:15 PM
    Monday, March 13, 2017 2:50 PM
  • Just for the record: in M, all objects have some type and they can (almost) all be assigned to a variable.

    In this case, tblName is not a table name, but a table.

    And it is qualified, as you would get an error if tblPDPmts didn't exist.

    tblPDPmts in turn is qualified by its column names and column types, and by any keys that may have been defined. (In this context, keys are not very exposed and I guess most people don't know about their mere existence, but when deep diving into the Power Query Formula Language you'll learn at some moment - probably when you're heavily grasping for breath - about table keys and that any defined keys are part of table types).

    • Edited by MarcelBeug Monday, March 13, 2017 3:39 PM
    • Marked as answer by jbt_PwrPvt Monday, March 13, 2017 9:02 PM
    Monday, March 13, 2017 3:37 PM

All replies

  • The first argument of Table.TransformColumnType must be a table, not a string.

    Probably if you remove the quotes around "tblPDPmts", you'll be fine, so:

    tblName = tblPDPmts,

    • Marked as answer by jbt_PwrPvt Monday, March 13, 2017 3:15 PM
    Monday, March 13, 2017 2:50 PM
  • Wow! See I told you it was something silly.  So I would have never thought to assign a variable a string name without quotes around it.  I guess M Code is different in that it knows the "type" of data without qualifying it in some way.

    Thanks for the quick response MarcelBeug!


    John Thomas

    Monday, March 13, 2017 3:18 PM
  • Just for the record: in M, all objects have some type and they can (almost) all be assigned to a variable.

    In this case, tblName is not a table name, but a table.

    And it is qualified, as you would get an error if tblPDPmts didn't exist.

    tblPDPmts in turn is qualified by its column names and column types, and by any keys that may have been defined. (In this context, keys are not very exposed and I guess most people don't know about their mere existence, but when deep diving into the Power Query Formula Language you'll learn at some moment - probably when you're heavily grasping for breath - about table keys and that any defined keys are part of table types).

    • Edited by MarcelBeug Monday, March 13, 2017 3:39 PM
    • Marked as answer by jbt_PwrPvt Monday, March 13, 2017 9:02 PM
    Monday, March 13, 2017 3:37 PM