cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WalkerGBG
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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:

edhans_0-1629076192909.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
WalkerGBG
Frequent Visitor

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.  🙂

Great @WalkerGBG - glad I was able to help.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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:

edhans_0-1629076192909.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!