DataFlow Loading Sharepoint Folder TSV Files - File Structure added new column that's now required

[WalkerGBG]

Hi

Have a really reliable Dataflow that refreshes everyday which loads hundreds of csv files.  Up till now they have had a standard structure.  Out business now need two additional fields which has been added to this file.

It's a field that now appears in the 2nd and 4th column of the file.  Everything else has stayed the same.  

These are fields that would be great to use in the PowerBI data that we import using this Dataflow.

Can anyone provide advice on how the DataFlow, PowerQuery can be amended to allow me to load all the older files AND the newer files with the new format.

At the moment I get errors if I try to rebuild the PowerQuery to include the column - it says Column not found for the files where it doesn't exist.  If I rebuild using the Original Template of the file I don't get the column.

THere's several topics on this similar subject but all attempts to see if they apply don't work

Here's the MCode for the original file layout

Setting up using OLD FILE FORMAT

let
  Source = SharePoint.Files("https://MYSITE", [ApiVersion = 15]),
  #"Filtered rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "FILTER1") and Text.Contains([Folder Path], "Live Data Test") and Text.Contains([Folder Path], "Daily")),
  #"Filtered rows 1" = Table.SelectRows(#"Filtered rows", each [Extension] = ".tsv"),
  #"Filtered hidden files" = Table.SelectRows(#"Filtered rows 1", each [Attributes]?[Hidden]? <> true),
  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content])),
  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),
  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file"}),
  #"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file"))),
  #"Changed column type" = Table.TransformColumnTypes(#"Expanded table column", {{"Source.Name", type text}, {"Customer", type text}, {"OLDFIELD1", type text}, {"EntryDateTime", type datetime}, {"OLDFIELD2", type text}, {"OLDFIELD3", type text}, {"DocumentType", type text}, {"DocumentName", type text}, {"CountryCode", type text}, {"CountryName", type text}, {"OLDFIELD4", type text}, {"OLDFIELD5", type text}, {"OLDFIELD6", type text}, {"Platform", type text}, {"OLDFIELD7", type text}, {"OLDFIELD8", type text}, {"OLDFIELD9", type text}})
in
  #"Changed column type"

Here's the MCode for the new file layout

SETTING UP USING NEW FILE FORMAT
  
  let
  Source = SharePoint.Files("https://MYSITE", [ApiVersion = 15]),
  #"Filtered rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "FILTER1") and Text.Contains([Folder Path], "Live Data Test") and Text.Contains([Folder Path], "Daily")),
  #"Filtered rows 1" = Table.SelectRows(#"Filtered rows", each [Extension] = ".tsv"),
  #"Sorted rows" = Table.Sort(#"Filtered rows 1", {{"Date created", Order.Descending}}),
  #"Filtered hidden files" = Table.SelectRows(#"Sorted rows", each [Attributes]?[Hidden]? <> true),
  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file (2)", each #"Transform file (2)"([Content])),
  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),
  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file (2)"}),
  #"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file (2)", Table.ColumnNames(#"Transform file (2)"(#"Sample file (2)"))),
  #"Changed column type" = Table.TransformColumnTypes(#"Expanded table column", {{"Source.Name", type text}, {"Customer", type text}, {"NEWFIELD1", type text}, {"OLDFIELD1", type text}, {"NEWFIELD2", Int64.Type}, {"EntryDateTime", type datetime}, {"OLDFIELD2", type text}, {"OLDFIELD3", type text}, {"DocumentType", type text}, {"DocumentName", type text}, {"CountryCode", type text}, {"CountryName", type text}, {"OLDFIELD4", type text}, {"OLDFIELD5", type text}, {"OLDFIELD6", type text}, {"Platform", type text}, {"OLDFIELD7", type text}, {"OLDFIELD8", type text}, {"OLDFIELD9", type text}})
in
  #"Changed column type"

ANy ideas how I can have one 'load' which will cater for these two additional columns.

[WalkerGBG]

Thank you for that.  Such a simple solution and worked.  Yes I have to do some work on the data types when bringing the data in but it doesn't argue or complain now when the new fields appear in newer file.

Really appreciate that.  Has happened to be several times before and I've accomodated by creating a second import process and then combining them together.  This is so much easier.  🙂

[edhans]

Great @WalkerGBG - glad I was able to help.

[edhans · Accepted solution]

Get rid of the Changed Type step, and make sure the transformations in the Sample Query and custom function are not doing an automatic changed type too. That will specifically reference a column and if it doesn't exist, you'll get errors.

You might consider redoing the new format, but turn this feature off in the desktop first:

You will have to do a bit more work, but you will avoid these issues. Even this isn't without a problem. When you combine files, go to your sample query, and if it should have a Promoted Headers line, you'll need to do that in the sample query, as this "never detect..." turns off both header promotion and type detection.