PBI Showing Null instead of actual value

[duc]

Hi

I have imported data from folder successfully to PBI, but one of columns showed null instead of its real data. 

Pleas anyone can help?

Here is in PBI

And here is the excel file

[Greg_Deckler]

@duc If you click the down arrow icon for that column, do you see any values other than null? What if you click the Load more values?

[duc]

@Greg_Deckler Yes, I did load all values. The whole column is null, while other columns are still good.

[Greg_Deckler]

@duc Any chance you can share your Excel file? Also, could you post the code from the Advanced Editor window?

[duc]

@Greg_Deckler  The code from advanced editor is as follow 

let
Source = Folder.Files("C:\Users\XXX\OneDrive - YYY\Shared folder\12. Data analysis\Data to PBI"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, 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}, {"No", Int64.Type}, {"DateKey", Int64.Type}, {"Order Code", type text}, {"Status Order Form", type text}, {"Department", type text}, {"Event Code", type any}, {"Name", type text}, {"Name-Eng", type text}, {"Event Date", type date}, , Int64.Type}, {" ANI Ethical event owner ", type text}})
in
#"Changed Type"

I have 2 similar excel files, for 2020 and 2021. The 2020 file worked normally while the format is the same. It's a big file with data, I am afraid I can not share, but if you need more info, I can provide. Sorry about that.

[duc]

@Greg_Deckler  I tried to load both files separately, they are all good. But when I put them into a folder and load the folder, only file 2020 worked; the 2021 file loaded but one column showed null. Appreciate if you can help!

[Greg_Deckler · Accepted solution]

@duc OK, well that explains the mystery. Almost guaranteed that you have a difference in your file formats like number of columns, etc. Thus, when you use a folder query and you use a sample file of 2020 then 2020 loads fine but 2021 is slightly different and therefore some transformation somewhere is messed up. I am guessing if you used 2021 as your sample then 2020 would be messed up somehow. Make 100% sure that your file formats are identical, same number of columns, same column names, etc.

[duc]

@Greg_Deckler  Thanks for your help! There is an additional space in column title, and that messed up.  Thanks a lot, Greg.

[Greg_Deckler]

@duc Always the little things!!