none
Trouble refreshing PowerQuery built DateTime tables after recent update RRS feed

  • Question

  • I have a custom DateTime table build entirely using PowerQuey. It worked fine until few weeks ago when I updated powery query. Now I keep getting Out of line Object error ( please refer to image below) whenever I try to refresh the workbook. how do I fix this.PowerQuery Error

    Here is the powerqury code i'm using

    let
        // Choose Start & End Date
        StartDate=#date(2013,1,1),
        EndDate=#date(2015,12,31),

       // Calculate number of dates
        NumberOfDates = Duration.Days(EndDate-StartDate),

        //Generate a continuous list of dates from the start date to the end date
        DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

        //Turn this list into a table
        TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}
                         , null, ExtraValues.Error),

        //Caste the single column in the table to type date
        ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),

        //Add custom columns for day of month, month number, year, etc etc
        Day = Table.AddColumn(ChangedType, "Day", each Date.Day([Date])),
        DayDoubleDigit = Table.AddColumn(Day, "DayDoubleDigit", each Date.ToText([Date],"dd")),

        MonthNumber = Table.AddColumn(DayDoubleDigit, "MonthNumber", each Date.Month([Date])),
        MonthName = Table.AddColumn(MonthNumber, "MonthName", each Date.ToText([Date],"MMMM")),
        MonthNameShort = Table.AddColumn(MonthName, "MonthNameShort", each Date.ToText([Date],"MMM")),

        Year = Table.AddColumn(MonthNameShort, "Year", each Date.Year([Date])),

        DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeek", each Date.DayOfWeek([Date])+1),
        DayName = Table.AddColumn(DayOfWeekNumber, "DayName", each Date.ToText([Date],"dddd")),
        DayNameShort = Table.AddColumn(DayName, "DayNameShort", each Date.ToText([Date],"ddd")),

        IsToday = Table.AddColumn(DayNameShort, "IsToday", each Date.IsInCurrentDay([Date])),
        // Week Starts on Monday
        IsInCurrentWeek = Table.AddColumn(IsToday,"IsInCurrentWeek",each if [Date] = Date.StartOfYear([Date]) then Date.IsInCurrentWeek([Date]) else Date.IsInCurrentWeek(Date.AddDays([Date],-1))),
        IsInCurrentMonth= Table.AddColumn(IsInCurrentWeek, "IsInCurrentMonth", each Date.IsInCurrentMonth([Date])),
        IsInCurrentYear= Table.AddColumn(IsInCurrentMonth, "IsInCurrentYear", each Date.IsInCurrentYear([Date])),

        // Week Starts on Monday
        IsInPreviousWeek = Table.AddColumn(IsInCurrentYear,"IsInPreviousWeek",each if [Date] = Date.StartOfYear([Date]) then Date.IsInPreviousWeek([Date]) else Date.IsInPreviousWeek(Date.AddDays([Date],-1))),
        IsInPreviousMonth= Table.AddColumn(IsInPreviousWeek, "IsInPreviousMonth", each Date.IsInPreviousMonth([Date])),
        IsInPreviousYear= Table.AddColumn(IsInPreviousMonth, "IsInPreviousYear", each Date.IsInPreviousYear([Date])),

        SemiMonthDaySet = Table.AddColumn(IsInPreviousYear, "SemiMonthDaySet", each (if [Day]<16 then "1-15" else Text.Combine({Text.From(16),Text.From(Date.Day(Date.EndOfMonth([Date])))},"-"))),

        SemiMonthCurrentDaySet = Table.AddColumn(SemiMonthDaySet, "SemiMonthCurrentDaySet", each if Date.Day(DateTime.LocalNow()) < 16 
    then (if Date.IsInCurrentMonth([Date]) = Logical.FromText("True") 
                                                           then (if Date.Day([Date]) < 16 
                                                                              then "TRUE" 
                                                                              else "FALSE") 
                                                           else "FALSE") 
    else (if Date.IsInCurrentMonth([Date]) = Logical.FromText("True")
                                                           then (if Date.Day([Date]) > 15 
                                                                                then "TRUE" 
                                                                                else "FALSE") 
                                                            else "FALSE")),
       
        SemiMonthPreviousDaySet = Table.AddColumn(SemiMonthCurrentDaySet, "SemiMonthPreviousDaySet", each if Date.Day(DateTime.LocalNow()) < 16 
    then (if Date.IsInPreviousMonth([Date]) = Logical.FromText("True") 
                                                           then (if Date.Day([Date]) > 15 
                                                                              then "TRUE" 
                                                                              else "FALSE") 
                                                           else "FALSE") 
    else (if Date.IsInCurrentMonth([Date]) = Logical.FromText("True")
                                                           then (if Date.Day([Date]) < 16 
                                                                                then "TRUE" 
                                                                                else "FALSE") 
                                                            else "FALSE")),

        QuarterOfYear= Table.AddColumn(SemiMonthPreviousDaySet, "QuarterOfYear",each Text.Combine({"Q", Text.From(Date.QuarterOfYear([Date]))}," ")),

        // Week Starts on Monday
        WeekOfYear = Table.AddColumn(QuarterOfYear,"WeekOfYear",each if [Date] = Date.StartOfYear([Date]) then Date.WeekOfYear([Date]) else Date.WeekOfYear(Date.AddDays([Date],-1))),
        WeekNameOfYear =Table.AddColumn(WeekOfYear ,"WeekNameOfYear ",each Text.Combine({"Week",Text.PadStart(Text.From([WeekOfYear]),2,"0")}," ")),
        // Returns first half of month as TRUE if the current date falls in 2nd half of month else returns previous month as TRUE
        SmartDateSelect = Table.AddColumn(WeekNameOfYear,"SmartDateSelect",each if Date.Day(DateTime.LocalNow()) > 15 then [SemiMonthPreviousDaySet] else [IsInPreviousMonth])
       
    in
        SmartDateSelect

    Tuesday, May 20, 2014 1:19 AM

Answers

  • I am happy to report that this is not an issue anymore. The problem turns put was something with the database server I was connecting to.. (Gupta SQLBase in this case)... Running a reorganize on the database server fixed this issue...

    For the index out of range error.. the issue was that power-query add-in would randomly disable itself... uninstalling & re-installing the office fixed this issue.

    Saturday, August 2, 2014 2:55 AM

All replies

  • Hi,

    This error (Microsoft.Mashup.OleDb.1 not registered) indicates that the Power Query provider is not properly set up in your machine.

    Could you try uninstalling Power Query and installing again? There's also a new update that we released this week and you might want to try: http://blogs.msdn.com/b/powerbi/archive/2014/05/29/what-s-new-in-power-query.aspx

    Hope this helps.

    Thanks,
    M.

    Saturday, May 31, 2014 5:48 PM
  • I uninstalled the PowerQuery and reinstalled the new update and it still has the same issue. 

    I've been been doing multiple scenarios to figure out where it's happening.

    1. Workbook only contains just the query itself: the query run fine and refreshes fine.

    2. the workbook contains the query and odbc data connection(in powerpivot) and i'm using refresh all to refresh everything: the query errors out with the error message [[I've tested this on multiple workbooks, some workbook with similar setup work but the one's that fail, they do it consistently]]

    3. the workbook contains the query and odbc data connection(in powerpivot) and i'm using refresh all to refresh everything except the powerquery: the refresh completes successfully [[ afterwords I can refresh the powerquery independently.]] 

    I'm running many powershell scripts that uses refresh all to refresh these workbooks on a set schedule so I'm trying to figure out a way to fix it so I don't need to refresh these workbooks manually.

    Sunday, June 1, 2014 7:06 PM
  • Sorry for the delay. Are you getting this error when invoking refresh from PowerShell or can you get it straight fro the UI without any scripting? If it's through PowerShell, it's possible that the addin isn't getting enough time to be activated (or it might not be activated at all when Excel is invoked like this.) One trial workaround would be add a wait in your PowerShell script after Excel is instantiated.
    Monday, June 16, 2014 10:56 PM
    Moderator
  • I get it from the UI. From powershell, it doesn't do anything (since I've set it up to run while suppressing errors)

    Monday, June 16, 2014 11:18 PM
  • Is this 32-bit or 64-bit Excel? And Excel 2010 or Excel 2013?

    Do I understand correctly that everything else works inside Power Query other than this one refresh case? What if you create a new workbook, paste in this query and then recreate the connection to PowerPivot; do you get the same error?

    Monday, June 16, 2014 11:36 PM
  • 32-bit Excel 2013
    Tuesday, June 17, 2014 12:02 AM
  • There are couple of workbooks that have the same issue. I'll try to recreate them to see if they still have this issue.
    Tuesday, June 17, 2014 2:18 AM
  • There are couple of workbooks that have the same issue. I'll try to recreate them to see if they still have this issue.

    Any progress?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, June 27, 2014 11:28 PM
    Owner
  • I recreated the workbook from scratch, this time i added another power query connection (this one just creates a simple table).. now i'm getting the error on both power query connections. exact same error as before.
    Thursday, July 3, 2014 2:10 AM
  • Now, I'm getting this error when I open the workbook

    Tuesday, July 8, 2014 2:48 AM
  • Okay. Does anybody have suggestions for this error?

    Bellicose, is it still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Saturday, August 2, 2014 2:32 AM
    Owner
  • I am happy to report that this is not an issue anymore. The problem turns put was something with the database server I was connecting to.. (Gupta SQLBase in this case)... Running a reorganize on the database server fixed this issue...

    For the index out of range error.. the issue was that power-query add-in would randomly disable itself... uninstalling & re-installing the office fixed this issue.

    Saturday, August 2, 2014 2:55 AM