cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TA-Analyst Frequent Visitor
Frequent Visitor

Data Loss Between Source and Query

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.

1 ACCEPTED SOLUTION

Accepted Solutions
TA-Analyst Frequent Visitor
Frequent Visitor

Re: Data Loss Between Source and Query

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.

3 REPLIES 3
zoloturu
Advisor

Re: Data Loss Between Source and Query

@TA-Analyst ,

 

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 Smiley Happy)

 

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

TA-Analyst Frequent Visitor
Frequent Visitor

Re: Data Loss Between Source and Query

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"

TA-Analyst Frequent Visitor
Frequent Visitor

Re: Data Loss Between Source and Query

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.