Data loss between file source and Power Query

[Seve]

Hello community,

I encounter a technical issue with data which have disappeared between the file source (xlsx) and the preview in Power Query… it concerns 4 items in text format. I confirm that data of those 4 items are in the source file 🙂 and that I don't use any function to cancel those concerned data in the applied steps. I don't find any explanation of this situation and it impacts the calculation of indicators in the report. Could you help me please? Thank you very much for your attention.

Best regards

[Seve]

Hello,

thank you very muh @edhans , @HotChilli , @KT_Bsmart2gethe , to have taken time to provide potential solutions to my issue. I can't share my data I'm sorry. I will try to get help within my company to terminate. I keep your precious solutions of course to continue to work on my issue but I can't share further. Best regards, and thank you again for your precious support.

[HotChilli]

Are these query steps auto-generated by powerbi? Are you using similar excel files in a folder as a source?

--

Can you try loading the single file into a new pbix using 'Excel workbook' as source to test?

--

I think I would need to look at the original excel sheet and trace it through the query steps to investigate further.  If you can put that and the pbix on a 3rd party site and link here, I'm sure myself or @edhans can help you.

[Seve]

Hello Hotchilli,

Thank you very for your idea. I confirm that the applied steps are automatically generated by the system progressively when I transform data. I uploaded the Excel source file via Excel wookbook directly from my personal directory of files and like that it worked effectively:  none data loss.  I join the corresponding scrennshot. I will try to send you the Excel file with the pbix.

[KT_Bsmart2gethe]

Hi @Seve,

There are usually data format such as " " that won't be considered empty. 

Regards

KT

[Seve]

Thank you for your replies. The file source is an xlsx file. Effectively there is the story of 100 first lines, but even when I filter to focus on the concerned perimeter, in the applied steps to check if I see the data, data are not displayed. In the screenhot attached, there is a screenshot of the file source with only 2 lines, just to simply illustrate the issue: the file contents information which are not displayed in the Power Query even there is no particular filter. In the screenshot with the applied steps, the filter "Filter rows2" exists only to illustrate the issue: it filters the same 2 lines of the source file, we see the corresponding data are null.

[edhans]

@Seve - in the Invoke Custom Function step it makes reference to one of the "Transform File*" folders - look at the Transform Sample File query that was autogenerated there. What may be happening is when that step was autogenerated these columns didn't exist, or had some other difference, and now they are being excluded downstream. You will need to look at that, because that query is feeding your main query where you see the data being filtered out somehow.

[Seve]

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"

[KT_Bsmart2gethe]

Hi @Seve ,

You are importing the files through "Import from folder", which means your transformation sample is based on the select transformation sample query, the sample you select as part of the import process.

There are several tracking points:

1. Are all files in the folder identical? (i.e. data format - from your screenshot above, it looks like there are duplications in the headers name), if not, there are potential losses due to different formats.

2. Your transformation sample query. Please check if any step that can potentially filter out the data or replace errors with null or skip. (i.e. if there are errors during the evaluation process, and if you tick the skip errors and data will be skipped. if this is the case then you will need to check the source)

Regards

KT

[edhans]

I cannot tell from that. There is no code there from the sample query, and without the source we are kinda guessing what these other sources are - though Left Outer joins will not cause data loss in the current query like an Inner would, or Right Outer might.

You have a lot going on in the filter step. Is the data there before the filter itself? Without the source, I cannot tell what it is/isn't filtering.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[edhans]

Is it in an XLS or XLSX file? If XLS, upgrade it to XLSX. If not, we need more info.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[HotChilli]

Missing rows, missing columns?

The preview will show 1000 rows as default so please check if the "data" is present from the data view in powerbi.  Can you confirm and show a picture please?