Data loss between file source and Power Query

Hello,

Thank you for your reply edhans,

Here is attached the code used in Advanced editor for the pbix in which there is a data loss. I wonder if it has been caused by 2 successive merger queries. At the begining of the building I combine 7 YYYY.xlsx files (one per year), transform data, then merg queries with 2 different xlsx files. All files are stored in Sharepoint. The issue concerns only the 2022.xlsx file. Here is the advanced editor script. Thank you again for your support.

let
    Source = SharePoint.Files("https://xxxxxxxx.sharepoint.com/sites/InternalControlBecauseweareincontrol/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx") and ([Folder Path] = "https://xxxxxxx.sharepoint.com/sites/InternalControlBecauseweareincontrol/Shared Documents/General/Internal Control/Data/") and ([Name] <> "2016.xlsx" and [Name] <> "2017.xlsx")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Process code", type text}, {"Process", type text}, {"Process complete name", type text}, {"Process name", type text}, {"Responsible", type any}, {"Accountable", type any}, {"Test activity", type text}, {"Control evaluation code", type text}, {"Generic control code", type text}, {"Assigned internal control code", type text}, {"Name", type text}, {"Description", type text}, {"Frequency", type text}, {"Assigned control name", type text}, {"Assessment levels", type text}, {"Self-Assessor", type text}, {"Department", type text}, {"Job", type text}, {"Evaluation N-1", type text}, {"SA comment", type text}, {"Controller comment", type text}, {"Status", type text}, {"Validation status code", type text}, {"Referential type", type text}, {"Entity", type text}, {"Entity complete name", type text}, {"User", type text}, {"Last modifier email", type text}, {"Last modification date", type datetime}, {"Documents", Int64.Type}, {"Internal risk code", type text}, {"Internal risk name", type text}, {"Risk code", type any}, {"Risk name", type any}, {"Weight", type any}, {"Code", type text}, {"Name_1", type text}, {"Period Code", Int64.Type}, {"Starting date", type any}, {"Ending date", type any}, {"Step", type any}, {"Associated test step", type any}, {"Documents count", type any}, {"Code_2", Int64.Type}, {"Test Narrative", type text}, {"Status_3", type text}, {"Conclusion", type text}, {"Test date", type any}, {"Tester login", type text}, {"Tester name", type text}, {"Tester email", type text}, {"Documents count_4", Int64.Type}, {"Test activity_5", type text}, {"Manager review date", type any}, {"Name_6", type text}, {"Code_7", type text}, {"Description_8", type text}, {"Priority", type text}, {"Recommendation by Internal Controller", type any}, {"Creation Date", type any}, {"Deadline", type any}, {"Real execution date", type datetime}, {"Responsible (in the system)", type text}, {"Responsible email", type text}, {"Status_9", type text}, {"Responsible (outside of the system)", type text}, {"Origin", type any}, {"Entity_10", type text}, {"Process_11", type text}, {"Status last update", type any}, {"Year of origin of non-conformity", type any}, {"Code_12", type text}, {"Name_13", type text}, {"Entity complete name_14", type text}, {"Parent OU code", type text}, {"Business Type", type text}, {"Business Process Owner", type text}, {"Ethics", type text}, {"Business Type_15", type text}, {"Fraud", type text}, {"Norms", type text}, {"Column82", type any}, {"Column83", type any}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([Process code] = "IST")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Process"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Process complete name", Splitter.SplitTextByDelimiter(" > ", QuoteStyle.Csv), {"Process complete name.1", "Process complete name.2", "Process complete name.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Process complete name.1", type text}, {"Process complete name.2", type text}, {"Process complete name.3", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Process complete name.1", "Process complete name.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Process complete name.2", "Active Control"}, {"Responsible", "Control Responsible"}, {"Accountable", "Control Accountable"}, {"Generic control code", "Control code"}, {"Name", "Control Name"}, {"Description", "Control Description"}, {"Frequency", "Control Frequency"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Assigned control name"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Department", "Self-Assessor Department"}, {"Job", "Self-Assessor Job"}, {"Controller comment", "SA Controller comment"}, {"Status", "SA Status"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"Validation status code"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns3", "Entity complete name", Splitter.SplitTextByDelimiter(" > ", QuoteStyle.Csv), {"Entity complete name.1", "Entity complete name.2", "Entity complete name.3", "Entity complete name.4", "Entity complete name.5", "Entity complete name.6", "Entity complete name.7", "Entity complete name.8", "Entity complete name.9", "Entity complete name.10", "Entity complete name.11", "Entity complete name.12", "Entity complete name.13"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Entity complete name.1", type text}, {"Entity complete name.2", type text}, {"Entity complete name.3", type text}, {"Entity complete name.4", type text}, {"Entity complete name.5", type text}, {"Entity complete name.6", type text}, {"Entity complete name.7", type text}, {"Entity complete name.8", type text}, {"Entity complete name.9", type text}, {"Entity complete name.10", type text}, {"Entity complete name.11", type text}, {"Entity complete name.12", type text}, {"Entity complete name.13", type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type2",{{"Entity complete name.3", "Organization Status"}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns3",{"Entity complete name.1"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns4",{{"Entity complete name.4", "L01-Group Company"}, {"Entity complete name.5", "L02-Surf Consolidation"}, {"Entity complete name.6", "L03-Time-Zone"}, {"Entity complete name.7", "L04-Region"}, {"Entity complete name.8", "L05-Country"}, {"Entity complete name.9", "L06-Company"}, {"Entity complete name.10", "L07-Town"}, {"Entity complete name.11", "L08-Surf Conso"}, {"Entity complete name.12", "L09-Accountable Organization"}, {"Entity complete name.13", "L10-Entity"}}),
    #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns4",{"Entity", "Entity complete name.2"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Removed Columns5",{{"Documents", "SA Documents"}, {"Last modification date", "SA Last modification date"}, {"Last modifier email", "SA Last modifier email"}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns5",{"Risk code", "Risk name", "Weight"}),
    #"Renamed Columns6" = Table.RenameColumns(#"Removed Columns6",{{"Code", "IC Campaign Code"}, {"Name_1", "IC Campaign Name"}, {"Period Code", "IC Campaign Period Code"}, {"Starting date", "IC Campaign Starting date"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns6",{{"IC Campaign Starting date", type date}, {"Ending date", type date}}),
    #"Renamed Columns7" = Table.RenameColumns(#"Changed Type3",{{"Ending date", "IC Campaign Ending date"}}),
    #"Removed Columns7" = Table.RemoveColumns(#"Renamed Columns7",{"Step", "Associated test step"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns7",{{"Code_2", type text}}),
    #"Renamed Columns8" = Table.RenameColumns(#"Changed Type4",{{"Code_2", "Test code"}, {"Status_3", "Test status"}, {"Conclusion", "Test Conclusion"}, {"Documents count_4", "Test Documents count"}}),
    #"Removed Columns8" = Table.RemoveColumns(#"Renamed Columns8",{"Test activity_5"}),
    #"Renamed Columns9" = Table.RenameColumns(#"Removed Columns8",{{"Manager review date", "Test Manager review date"}, {"Name_6", "AP Name"}, {"Code_7", "AP Code"}, {"Description_8", "AP Description"}, {"Priority", "AP Priority"}, {"Recommendation by Internal Controller", "AP Recommendation by Internal Controller"}, {"Creation Date", "AP Creation Date"}}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Renamed Columns9",{{"AP Creation Date", type date}, {"Deadline", type date}}),
    #"Renamed Columns10" = Table.RenameColumns(#"Changed Type5",{{"Deadline", "AP Deadline"}, {"Real execution date", "AP Real execution date"}, {"Responsible (in the system)", "AP Responsible (in the system)"}, {"Responsible email", "AP Responsible email"}, {"Status_9", "AP Status"}, {"Responsible (outside of the system)", "AP Responsible (outside of the system)"}, {"Origin", "AP Origin"}, {"Status last update", "AP Status last update"}}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns10",{{"AP Status last update", type datetime}}),
    #"Removed Columns9" = Table.RemoveColumns(#"Changed Type6",{"Process_11", "Entity_10"}),
    #"Renamed Columns11" = Table.RenameColumns(#"Removed Columns9",{{"Code_12", "Entity Code"}}),
    #"Removed Columns10" = Table.RemoveColumns(#"Renamed Columns11",{"Column82", "Column83", "Business Type"}),
    #"Renamed Columns12" = Table.RenameColumns(#"Removed Columns10",{{"Business Type_15", "Business Type"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns12",{"IC Campaign Period Code", "Source.Name", "Process code", "Active Control", "Process name", "Control Responsible", "Control Accountable", "Test activity", "Control evaluation code", "Control code", "Assigned internal control code", "Control Name", "Control Description", "Control Frequency", "Assessment levels", "Self-Assessor", "Self-Assessor Department", "Self-Assessor Job", "Evaluation N-1", "SA comment", "SA Controller comment", "SA Status", "Referential type", "Organization Status", "L01-Group Company", "L02-Surf Consolidation", "L03-Time-Zone", "L04-Region", "L05-Country", "L06-Company", "L07-Town", "L08-Surf Conso", "L09-Accountable Organization", "L10-Entity", "User", "SA Last modifier email", "SA Last modification date", "SA Documents", "Internal risk code", "Internal risk name", "IC Campaign Code", "IC Campaign Name", "IC Campaign Starting date", "IC Campaign Ending date", "Documents count", "Test code", "Test Narrative", "Test status", "Test Conclusion", "Test date", "Tester login", "Tester name", "Tester email", "Test Documents count", "Test Manager review date", "AP Name", "AP Code", "AP Description", "AP Priority", "AP Recommendation by Internal Controller", "AP Creation Date", "AP Deadline", "AP Real execution date", "AP Responsible (in the system)", "AP Responsible email", "AP Status", "AP Responsible (outside of the system)", "AP Origin", "AP Status last update", "Year of origin of non-conformity", "Entity Code", "Name_13", "Entity complete name_14", "Parent OU code", "Business Process Owner", "Ethics", "Business Type", "Fraud", "Norms"}),
    #"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each ([Process code] = "IST") and ([#"L02-Surf Consolidation"] = "Information Systems - Corporate" or [#"L02-Surf Consolidation"] = "Op Dir Manufacturing" or [#"L02-Surf Consolidation"] = "Op Dir Research and Developement" or [#"L02-Surf Consolidation"] = "Op Dir Research and Development") and ([#"L08-Surf Conso"] = "Op Dir Manuf - Site Management" or [#"L08-Surf Conso"] = "Op Dir Manufacturing" or [#"L08-Surf Conso"] = "Op Dir Research and Developement" or [#"L08-Surf Conso"] = "Op Dir Research and Development - Site Management")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows1", {"Assigned internal control code"}, AP_Previous_Period, {"Control Evaluation Code"}, "AP_Previous_Period", JoinKind.LeftOuter),
    #"Expanded AP_Previous_Period" = Table.ExpandTableColumn(#"Merged Queries", "AP_Previous_Period", {"Source.Name", "IC Campaign Period", "Control Code", "L10 - Entity", "Control Evaluation Code", "Assessment Levels", "Test Conclusion", "AP Code", "AP Status", "AP Deadline"}, {"AP_Previous_Period.Source.Name", "AP_Previous_Period.IC Campaign Period", "AP_Previous_Period.Control Code", "AP_Previous_Period.L10 - Entity", "AP_Previous_Period.Control Evaluation Code", "AP_Previous_Period.Assessment Levels", "AP_Previous_Period.Test Conclusion", "AP_Previous_Period.AP Code", "AP_Previous_Period.AP Status", "AP_Previous_Period.AP Deadline"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AP_Previous_Period", "Site", each Text.End([#"L10-Entity"],3)),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom", {"L10-Entity"}, #"Equivalences LINCX DOMF", {"L10 - Entity"}, "Equivalences LINCX DOMF", JoinKind.LeftOuter),
    #"Expanded Equivalences LINCX DOMF" = Table.ExpandTableColumn(#"Merged Queries1", "Equivalences LINCX DOMF", {"MO Hôte", "MO Metier", "UOT", "Site - Trigramme"}, {"MO Hôte", "MO Metier", "UOT", "Site - Trigramme"}),
    #"Renamed Columns13" = Table.RenameColumns(#"Expanded Equivalences LINCX DOMF",{{"MO Hôte", "Hosting MO"}}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns13",{{"Site", type text}}),
    #"Removed Columns11" = Table.RemoveColumns(#"Changed Type7",{"Referential type", "User"}),
    #"Changed Type8" = Table.TransformColumnTypes(#"Removed Columns11",{{"SA Last modification date", type date}}),
    #"Removed Columns12" = Table.RemoveColumns(#"Changed Type8",{"IC Campaign Code", "Documents count"}),
    #"Changed Type9" = Table.TransformColumnTypes(#"Removed Columns12",{{"Test date", type date}, {"Test Manager review date", type date}, {"AP Real execution date", type date}, {"AP Status last update", type date}}),
    #"Removed Columns13" = Table.RemoveColumns(#"Changed Type9",{"Year of origin of non-conformity", "Entity Code", "Name_13", "Entity complete name_14", "Parent OU code"})
in
    #"Removed Columns13"