Data Loss Between Source and Query

[Anonymous]

I am having an issue similar to this post.

My source is a folder with several Excel files. I am combining the data from all of the Excel files. when I do so everything populates as expected except for one single cell. It dispalys as null. It is a data colum in which other dates populate correctly.  I have tried to change the date of the source cell and I get the same results. With multiple Power BI files, I have tried refreshing, reimporting, and even the Advance Editor trick mentioned in the above post but nothing has solved the issue.

Any assistance would be greatly apprecaited.

[Anonymous · Accepted solution]

It appears that the header of the individual Excel files impacted this issue. Perhaps because both the header and the data were in column A? Regardless, I altered the files I was using to eliminate the header. It began to work as expected with that change.

[zoloturu]

@Anonymous ,

1. Try to add any simple step to that query in PowerQuery editor. For instance, rearrange two columns or any other simple step. And hit apply.

2. If item #1 will not help then post code of this query here (in readable view, please :))

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

[Anonymous]

I have tried adding a step and it did not result in the cell in question to populate.

Please see below for the code:

let
Source = Folder.Files("FOLDER_LOCATION"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Candidate Tracking Log", each #"Transform File from Candidate Tracking Log"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Candidate Tracking Log"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Candidate Tracking Log", Table.ColumnNames(#"Transform File from Candidate Tracking Log"(#"Sample File"))),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Candidate Name", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Candidate Name] = "Test 1" or [Candidate Name] = "Test 2")),
#"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"Candidate Name", Order.Ascending}, {"DO NOT EDIT", Order.Ascending}})
in
#"Sorted Rows1"