[UPDATED !] PowerQuery Refreshing List.Dates query error

[Anonymous]

Hello everybody!

I'm having a problem when refreshing a "PowerQuery " query after migrating the working Excel file to a new computer.

The Excel and PQ versions kept the same : EXCEL 2016 MSO (16.0.4266.1001) / PQ 2.24.4165.1401

The steps I followed were:

1. In Excel, I created a new blank query (named it as Query1) from Data -> New Query -> From Other Sources -> Blank Query

  = List.Dates

2. Created a new Query (named it as dCalendar) as before then went to Advanced Editor:

let
Source = Query1(#date(2022, 1, 1), 00100, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DataBase"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([DataBase]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([DataBase]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([DataBase]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Quarter",{"Quarter"}),
#"Inserted Day" = Table.AddColumn(#"Removed Columns", "Day", each Date.Day([DataBase]), Int64.Type),
#"Inserted Quarter1" = Table.AddColumn(#"Inserted Day", "Quarter", each Date.QuarterOfYear([DataBase]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter1", "Week of Year", each Date.WeekOfYear([DataBase]), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Week of Year",{{"Year", "Year"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "MonthName", each Date.ToText([DataBase],"MMMM")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "DayName", each Date.ToText([DataBase],"dddd")),
#"Inserted Merged Column" = Table.AddColumn(#"Added Custom1", "YearQuarter_Name", each Text.Combine({Text.From([Year], "pt-PT"), Text.From([Quarter], "pt-PT")&"º Trimestre"}, " - "), type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"DataBase", type date}}),
#"Inserted Merged Column1" = Table.AddColumn(#"Changed Type", "AnoMes", each Text.Combine({Text.From([Year], "pt-PT"), [MonthName]}, " - "), type text),
#"Inserted Merged Column2" = Table.AddColumn(#"Inserted Merged Column1", "YearMonth", each Text.Combine({Text.From([Year], "pt-PT"), Text.From([Month], "pt-PT")}, " - "), type text)
in
#"Inserted Merged Column2"

When refreshing this last one an error message is triggered.
However, the firts one, when invoked alone it returns the date list correctly.

Please check the images attached.

Any clues about how to solve this ?

Thank you all!

[v-kkf-msft]

Hi @Anonymous ,

When I use version 2206 Build 16.0.15330.20144 64-bit, it loads the query correctly. Please try upgrading to a newer version or use the List.Dates in the dCalendar query. 

Best Regards,
Winniz

[Anonymous]

Hello @v-kkf-msft.

Thank you for your answer.

Meanwhile I found that the problem is not only with this connection to List.Dates.
I tried to connect also via "Get & Transform" menu > New Query  > From Database > From SQL Server Database and ... bingo, the error is the same!

Conclusion: a problem with the provider PowerQuery provider:  Provider=Microsoft.Mashup.OleDb.1

Possibly IT Dept must update Excel version.