PBI - all data loaded are null

[duc]

Hi

I have loaded data from folder consisting 4 excel files. The column titles are the same. After several changes, except for sample file, the others showed null when loading to PBI.

let
Source = Folder.Files("Shared folder\Data analysis\Speaker Data\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"))),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Speaker data 2021.xlsx", "Source.Name"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Source.Name", type text}, {"No", Int64.Type}, {"Datekey", Int64.Type}...
in
#"Changed Type"

When I create a new PBI file from the same folder, evertything are good.

Anyone please can help? Thanks a lot.

[Greg_Deckler]

@duc Somebody else just recently had this issue, the problem turned out to be an extra space in one of their column headers. It's almost guaranteed that the file formats are different in some way. What happens if you use a different sample file? 

[duc]

@Greg_Deckler  Thanks Greg. I have copied the title column to other files to ensure they are the same.

Also, I have tried to change sample file as your advice, the issue is the same - sample file is okay while others are null.

[Greg_Deckler]

@duc So do you really have this same column in every file?

#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Speaker data 2021.xlsx", "Source.Name"}}),

[duc]

@Greg_Deckler  I am pretty sure that I have the same columns in every file, as I did copy the column title from sample file to others.

Additionally, when I run a new PBI file, all data sources are normally loaded. Just current PBI file mess up

[Greg_Deckler]

@duc It seems like you have extra steps in your Folder query that probably shouldn't be there. Any transformations that you want to add to importing the files should go in your Transform Sample File query, not the Folder query. So, your Folder query should always be something like:

let
    Source = Folder.Files("C:\Users\gdeck\OneDrive\Documents\Folder"),
    #"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}, {"One", Int64.Type}, {"Two", Int64.Type}, {"Three", Int64.Type}})
in
    #"Changed Type"

These two steps seem to not belong:

#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Speaker data 2021.xlsx", "Source.Name"}}),

[duc]

@Greg_Deckler  I have deleted the 02 extra steps as per your suggestion, but another error come up. Can you advise how to fix?

[Greg_Deckler]

@duc Remove the comma at the end of the line just above your "in" statement.

[duc]

@Greg_Deckler  Thank Greg, but look like I do not have any comma at the end. 

[Greg_Deckler]

@duc Or add a comma at the end? Paste in everything from the Advanced Editor and I can try to sort through it.

[duc · Accepted solution]

Thank Greg for very helpful. I tried, but still can not fix the issue. 

I loaded the PBI from the begining and re-build the dashboard. All are good.

Anyway, thanks for your help, as always.