Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Error Dataflow Refresh - column wasn't found

Hi,

 

My dataflow consolidate files in a folder and creates a query. Error encountered is when a set of files have different columns from the last dataflow refresh. 

Example: First refresh files have Col1, Col2, Col3, Col4 headers, then next refresh different set of files will have Col1, Col3, Col4 -- Ill get a The column 'Col2' of the table wasn't found error.

Current workaround I do is to open the dataflow and save it again (no code changes done) then when I perform dataflow refresh again I dont encounter the error. Is this some kind of caching?

I use below step to expand the columns dynamically as the files being consolidated may have different columns from time to time.

 

 


Table.ExpandTableColumn(#"Removed columns", "Transform file",Table.ColumnNames(Table.Combine(#"Removed columns"[#"Transform file"])))

 

 
 
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    Modify your query to check for the existence of the column before expanding it. 

    let
    Source = #"Removed columns",
    AllTransformFiles = Source[#"Transform file"],
    CombinedFiles = Table.Combine(AllTransformFiles),
    AllColumnNames = Table.ColumnNames(CombinedFiles),
    ExistingColumns = List.Intersect({AllColumnNames, {"Col1", "Col2", "Col3", "Col4"}}),
    ExpandedTable = Table.ExpandTableColumn(Source, "Transform file", ExistingColumns)
in
    ExpandedTable

    Or you could use the "Remove Other Columns" transformation to remove any columns that are not present in the current file, and then use the "Rename Columns" transformation to ensure that the column names match across all files. This would ensure that the schema is always up-to-date and matches the current set of files.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Modify your query to check for the existence of the column before expanding it. 

    let
    Source = #"Removed columns",
    AllTransformFiles = Source[#"Transform file"],
    CombinedFiles = Table.Combine(AllTransformFiles),
    AllColumnNames = Table.ColumnNames(CombinedFiles),
    ExistingColumns = List.Intersect({AllColumnNames, {"Col1", "Col2", "Col3", "Col4"}}),
    ExpandedTable = Table.ExpandTableColumn(Source, "Transform file", ExistingColumns)
in
    ExpandedTable

    Or you could use the "Remove Other Columns" transformation to remove any columns that are not present in the current file, and then use the "Rename Columns" transformation to ensure that the column names match across all files. This would ensure that the schema is always up-to-date and matches the current set of files.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.