CODEWITHSUNDEEP
azurepowerbi
priya
priya

Reputation: 93

Load multiple files from a folder with missing column Power BI

I am trying to load and combine multiple json files (from a single folder) into Power BI. Few files have a missing column. Suppose, File1 has 20columns, where File2 has only 19columns. Column Name are same.

But when combining and loading the files Power BI throws an error for the missing column: An error occurred in the ‘Transform File (4)’ query. Expression.Error: The column 'col3' of the table wasn't found.

    Source = AzureStorage.DataLake("Folder-Path"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (4)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"id", type any}, {"ref", type any}, {"col3", type any}})
in
    #"Changed Type"```

Upvotes: 1

Views: 819

Answers (1)

M Almond
M Almond

Reputation: 11

I actually came here looking for an answer to a similar question.

this link will explain some more of the process: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries

When using the Helper Queries created by Power BI to combine files, A sample file will be chosen. Then you can apply steps to that file.

As you have stated some of the files are missing a column. When the change type is applied it is looking for specific header names. If it can't find one (case sensitive) the load will fail.

Removing the #"Changed Type" step or fixing the headers in the culprit files will fix this issue.

You could also remove {"col3", type any} from the #"Changed Type" step.

#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"id", type any}, {"ref", type any}})

Not to hijack the question, but my issue is when the sample file has less columns than another file in the folder, it will only load as many columns as the sample file has, which can result in missing columns. My fix was to choose a sample file with the most columns.

Upvotes: 1

Related Questions