Power Query刷新时打开的源文件日期自动变为序列号问题求助

我创建了一个从文件夹获取数据的Power Query，该文件夹包含2个带有日期列的xlsb格式Excel文件。当打开源文件1并刷新查询时，文件1的日期格式自动转换为Excel序列号（例如15/06/2015变为38518），而未打开的源文件2的日期仍保持DD/MM/YYYY格式。此时Power Query无法将该列转换回DD/MM/YYYY日期格式，且该问题仅在源文件处于打开状态时出现。

原查询代码：

let  Source = Folder.Files("H:\Test"),
Refresh_When_Excel_Source_Opened = Table.SelectRows(Source, each not Text.StartsWith([Name], "~$")),
Add_Get_Data_From_Content_Column = Table.AddColumn(Refresh_When_Excel_Source_Opened, "Get_Data_From_Content", each Excel.Workbook([Content])),
Keep_Only_Get_Data_From_Content_Column = Table.SelectColumns(Add_Get_Data_From_Content_Column,{"Get_Data_From_Content"}),
Expand_Get_Data_Column = Table.ExpandTableColumn(Keep_Only_Get_Data_From_Content_Column, "Get_Data_From_Content", {"Name", "Data"}, {"Get_Data.Name", "Get_Data.Data"}),
Filter_Only_BDD_Tables = Table.SelectRows(Expand_Get_Data_Column, each Text.StartsWith([Get_Data.Name], "BDD")),
Expand_Composed_Data = Table.ExpandTableColumn(Filter_Only_BDD_Tables, "Get_Data.Data", {"Statut", "Secteur du juge", "Référence Juge", "Code IODAS ", "Code GED", "Nom", "Prénom", "Genre", "Date de naissance", "Référent TS", "Référent administratif", "Date admission au SEMNA", "Type de mesure en cours", "Date du début de la mesure", "Date de fin de la  mesure", "Lieu de placement actuel", "Echéance CSS", "Observations CPAM", "Date envoi du kit APJM", "Date de dépôt de la demande SG", "Notification SEJM", "Compte bancaire", "Observations diverses", "Date de notification de Reconnaissance MDPH", "Dossier PPE ", "Dossier DDE TDS déposé", "Date de dépôt", "Dépôt de DDE TDS à Paris", "Type de titre", "Réponse préfecture", "Autres démarches liées au séjour", "Démarches état civil auprès du pays origine ou consulat", "Observations"}, {"Statut", "Secteur du juge", "Référence Juge", "Code IODAS ", "Code GED", "Nom", "Prénom", "Genre", "Date de naissance", "Référent TS", "Référent administratif", "Date admission au SEMNA", "Type de mesure en cours", "Date du début de la mesure", "Date de fin de la  mesure", "Lieu de placement actuel", "Echéance CSS", "Observations CPAM", "Date envoi du kit APJM", "Date de dépôt de la demande SG", "Notification SEJM", "Compte bancaire", "Observations diverses", "Date de notification de Reconnaissance MDPH", "Dossier PPE ", "Dossier DDE TDS déposé", "Date de dépôt", "Dépôt de DDE TDS à Paris", "Type de titre", "Réponse préfecture", "Autres démarches liées au séjour", "Démarches état civil auprès du pays origine ou consulat", "Observations"}),
#"Renamed Columns" = Table.RenameColumns(Expand_Composed_Data, {{Table.ColumnNames(Expand_Composed_Data){0},"Pôle"}}),
Change_Data_Type = Table.TransformColumnTypes(#"Renamed Columns",{{"Statut", type text}, {"Secteur du juge", type text}, {"Référence Juge", type text}, {"Code IODAS ", type text}, {"Code GED", type text}, {"Nom", type text}, {"Prénom", type text}, {"Genre", type text}, {"Date de naissance", type date}, {"Référent TS", type text}, {"Référent administratif", type text}, {"Date admission au SEMNA", type date}, {"Type de mesure en cours", type text}, {"Date du début de la mesure", type date}, {"Date de fin de la  mesure", type date}, {"Lieu de placement actuel", type text}, {"Echéance CSS", type date}, {"Observations CPAM", type text}, {"Date envoi du kit APJM", type date}, {"Date de dépôt de la demande SG", type date}, {"Notification SEJM", type text}, {"Compte bancaire", type text}, {"Observations diverses", type text}, {"Date de notification de Reconnaissance MDPH", type date}}),
Exclude_Headers_Other_Columns = Table.SelectRows(Change_Data_Type, each [Statut] <> "Statut"),
Keep_Only_Necessary_Columns = Table.SelectColumns(Exclude_Headers_Other_Columns,{"Pôle","Statut", "Secteur du juge", "Référence Juge", "Code IODAS ", "Code GED", "Nom", "Prénom", "Genre", "Date de naissance", "Référent TS", "Référent administratif", "Date admission au SEMNA", "Type de mesure en cours", "Date du début de la mesure", "Date de fin de la  mesure", "Lieu de placement actuel", "Echéance CSS", "Observations CPAM", "Date envoi du kit APJM", "Date de dépôt de la demande SG", "Notification SEJM", "Compte bancaire", "Observations diverses", "Date de notification de Reconnaissance MDPH"})
in Keep_Only_Necessary_Columns

• 当Excel文件处于打开状态时，Power Query读取的是内存中的实时数据，此时日期会以Excel内部存储的OLE自动化日期（序列号）形式返回，而非磁盘文件里的格式化文本。
• 原代码直接用Table.TransformColumnTypes转换会失败，因为同一列混合了文本格式的日期和数值格式的序列号，类型不统一导致转换异常。

需要先统一处理所有日期列，不管是文本日期还是数值序列号，都转换成标准日期类型：

1. 先排除表头行，避免文本表头干扰日期转换
2. 自定义函数处理单个日期值：先转成文本，判断是否为数字（序列号），是则用Date.FromOADate转换，否则用指定的DD/MM/YYYY格式解析文本日期
3. 批量应用自定义函数处理所有日期列，再统一设置其他列的文本类型

let
    Source = Folder.Files("H:\Test"),
    Refresh_When_Excel_Source_Opened = Table.SelectRows(Source, each not Text.StartsWith([Name], "~$")),
    Add_Get_Data_From_Content_Column = Table.AddColumn(Refresh_When_Excel_Source_Opened, "Get_Data_From_Content", each Excel.Workbook([Content])),
    Keep_Only_Get_Data_From_Content_Column = Table.SelectColumns(Add_Get_Data_From_Content_Column, {"Get_Data_From_Content"}),
    Expand_Get_Data_Column = Table.ExpandTableColumn(Keep_Only_Get_Data_From_Content_Column, "Get_Data_From_Content", {"Name", "Data"}, {"Get_Data.Name", "Get_Data.Data"}),
    Filter_Only_BDD_Tables = Table.SelectRows(Expand_Get_Data_Column, each Text.StartsWith([Get_Data.Name], "BDD")),
    Expand_Composed_Data = Table.ExpandTableColumn(Filter_Only_BDD_Tables, "Get_Data.Data", {"Statut", "Secteur du juge", "Référence Juge", "Code IODAS ", "Code GED", "Nom", "Prénom", "Genre", "Date de naissance", "Référent TS", "Référent administratif", "Date admission au SEMNA", "Type de mesure en cours", "Date du début de la mesure", "Date de fin de la  mesure", "Lieu de placement actuel", "Echéance CSS", "Observations CPAM", "Date envoi du kit APJM", "Date de dépôt de la demande SG", "Notification SEJM", "Compte bancaire", "Observations diverses", "Date de notification de Reconnaissance MDPH", "Dossier PPE ", "Dossier DDE TDS déposé", "Date de dépôt", "Dépôt de DDE TDS à Paris", "Type de titre", "Réponse préfecture", "Autres démarches liées au séjour", "Démarches état civil auprès du pays origine ou consulat", "Observations"}, {"Statut", "Secteur du juge", "Référence Juge", "Code IODAS ", "Code GED", "Nom", "Prénom", "Genre", "Date de naissance", "Référent TS", "Référent administratif", "Date admission au SEMNA", "Type de mesure en cours", "Date du début de la mesure", "Date de fin de la  mesure", "Lieu de placement actuel", "Echéance CSS", "Observations CPAM", "Date envoi du kit APJM", "Date de dépôt de la demande SG", "Notification SEJM", "Compte bancaire", "Observations diverses", "Date de notification de Reconnaissance MDPH", "Dossier PPE ", "Dossier DDE TDS déposé", "Date de dépôt", "Dépôt de DDE TDS à Paris", "Type de titre", "Réponse préfecture", "Autres démarches liées au séjour", "Démarches état civil auprès du pays origine ou consulat", "Observations"}),
    #"Renamed Columns" = Table.RenameColumns(Expand_Composed_Data, {{Table.ColumnNames(Expand_Composed_Data){0}, "Pôle"}}),
    // 先排除表头行，避免干扰日期转换
    Exclude_Headers_Other_Columns = Table.SelectRows(#"Renamed Columns", each [Statut] <> "Statut"),
    // 自定义函数：兼容序列号和文本日期的转换逻辑
    ConvertToDate = (value as any) as date =>
        let
            textValue = Text.From(value, "fr-FR"),
            isNumber = try Number.From(textValue) otherwise null,
            result = if isNumber <> null then Date.FromOADate(isNumber) else Date.FromText(textValue, [Format="dd/MM/yyyy", Culture="fr-FR"])
        in
            result,
    // 批量处理所有日期列
    ProcessDateColumns = Table.TransformColumns(Exclude_Headers_Other_Columns, {
        {"Date de naissance", ConvertToDate},
        {"Date admission au SEMNA", ConvertToDate},
        {"Date du début de la mesure", ConvertToDate},
        {"Date de fin de la  mesure", ConvertToDate},
        {"Echéance CSS", ConvertToDate},
        {"Date envoi du kit APJM", ConvertToDate},
        {"Date de dépôt de la demande SG", ConvertToDate},
        {"Date de notification de Reconnaissance MDPH", ConvertToDate}
    }),
    // 设置其他列的文本类型
    SetOtherColumnTypes = Table.TransformColumnTypes(ProcessDateColumns, {
        {"Pôle", type text}, {"Statut", type text}, {"Secteur du juge", type text}, {"Référence Juge", type text},
        {"Code IODAS ", type text}, {"Code GED", type text}, {"Nom", type text}, {"Prénom", type text},
        {"Genre", type text}, {"Référent TS", type text}, {"Référent administratif", type text},
        {"Type de mesure en cours", type text}, {"Lieu de placement actuel", type text}, {"Observations CPAM", type text},
        {"Notification SEJM", type text}, {"Compte bancaire", type text}, {"Observations diverses", type text}
    }),
    Keep_Only_Necessary_Columns = Table.SelectColumns(SetOtherColumnTypes, {"Pôle","Statut", "Secteur du juge", "Référence Juge", "Code IODAS ", "Code GED", "Nom", "Prénom", "Genre", "Date de naissance", "Référent TS", "Référent administratif", "Date admission au SEMNA", "Type de mesure en cours", "Date du début de la mesure", "Date de fin de la  mesure", "Lieu de placement actuel", "Echéance CSS", "Observations CPAM", "Date envoi du kit APJM", "Date de dépôt de la demande SG", "Notification SEJM", "Compte bancaire", "Observations diverses", "Date de notification de Reconnaissance MDPH"})
in
    Keep_Only_Necessary_Columns

• 自定义函数ConvertToDate会自动识别输入值的类型，无论是Excel序列号还是DD/MM/YYYY格式的文本，都能转换成标准日期类型
• 调整步骤顺序，先排除表头再处理日期，避免表头文本导致转换错误
• 单独处理日期列后再设置其他列类型，确保所有数据类型转换准确

内容的提问来源于stack exchange，提问作者vtnnn