Power Query - COM-Error due to month & year extraction from date

[Daugaard92]

Hello everyone.

For some reason, I keep getting an error, when I try to add two columns (year and month) based on the value in a "date" column.

The error appears once I try to establish a "Data-connection" and add the data to my Data Model.
I apologise for the language below being in Danish. The data is collected from several Excel files within a folder.

Rough translation is:
"COM-error: mscorlib; The value, which was to be added, lay outside of the area.
Parametername: value.
The actual action was cancelled, as another action in the transaction was unsuccesful."

The advanced editor-log is as follows:

let
Kilde = Folder.Files("N:\Økonomi\Engrosafregning\Data\Dataudtræk fra DataHub\Data"),
#"Skjulte filer er filtreret1" = Table.SelectRows(Kilde, each [Attributes]?[Hidden]? <> true),
#"Aktivér brugerdefineret funktion1" = Table.AddColumn(#"Skjulte filer er filtreret1", "Transformér fil (2)", each #"Transformér fil (2)"([Content])),
#"Omdøbte kolonner1" = Table.RenameColumns(#"Aktivér brugerdefineret funktion1", {"Name", "Source.Name"}),
#"Fjernede andre kolonner1" = Table.SelectColumns(#"Omdøbte kolonner1", {"Source.Name", "Transformér fil (2)"}),
#"Udvidet tabelkolonne1" = Table.ExpandTableColumn(#"Fjernede andre kolonner1", "Transformér fil (2)", Table.ColumnNames(#"Transformér fil (2)"(#"Eksempelfil (2)"))),
#"Ændret type" = Table.TransformColumnTypes(#"Udvidet tabelkolonne1",{{"Source.Name", type text}, {"ENERGYBUSINESSPROCESS", type text}, {"PROCESSVARIANT", type text}, {"METERINGGRIDAREAID", Int64.Type}, {"BALANCESUPPLIERID", Int64.Type}, {"STARTDATETIME", type datetime}, {"RESOLUTIONDURATION", type duration}, {"TYPEOFMP", type text}, {"SETTLEMENTMETHOD", type text}, {"MEASUREUNIT", type text}, {"ENERGYCURRENCY", type text}, {"ENERGYQUANTITY", type number}, {"PRICE", type number}, {"AMOUNT", type number}, {"CHARGETYPE", type text}, {"PARTYCHARGETYPEID", type text}, {"CHARGETYPEOWNERID", Int64.Type}, {"VERSION", type any}, {"DatoCSV1H", type datetime}, {"DATO", type datetime}}),
#"Indsat år" = Table.AddColumn(#"Ændret type", "År", each Date.Year([STARTDATETIME]), Int64.Type),
#"Indsatte navn på måned" = Table.AddColumn(#"Indsat år", "Navn på måned", each Date.MonthName([STARTDATETIME]), type text)
in
#"Indsatte navn på måned"

I have been struggling with this for hours now and can't find a fix - It's weird, as it has worked for me before. Might someone know what's causing this to happen?

 

[Alex Blakenburg]

If it’s worked for you before, do you have a previous version of the excel file and previous version of the data files ?
Dump the previous code and this code side by side in excel and see if anything has changed.
Open the old and new data files in excel and see if they have changed in headings, columns & data formatting.