Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGet 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |