Transform Sample File: Add Column>Custom Column to add "File Name"

[ryan1919]

PBIC,

I have a Power BI Desktop file, and the data is sourced from multiple .csv files within a single folder.

Within Power Query, I am attempting to add a new custom column that would show the file name.

I would like this to occur within the "Helper Queries" called Transform Sample File, so that when in the future I add new .csv files to the folder, the file name will be auto-added.

Quick note: below in "Other Queries" SS_GmapsExtractor, there is a column that shows the File Name:

and in the formula bar shows:

= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

Ultimately I would like this File Name to be added to Transform Sample File.  I am not sure how to do it.

TYIA,

Ryan

[v-jingzhang]

Hi @ryan1919 

I'm afraid this is difficult to achieve. In the current "Transform Sample File" query, you will see the first Source step is Source = Csv.Document(Parameter1,[Delimiter=",", Columns=29, Encoding=65001, QuoteStyle=QuoteStyle.Csv]), it gets data from a parameter Parameter1. If you go to check the current value of the parameter, you will see its value is a binary value which comes from the [Content] column. The [Content] column contains the data inside the file while the file name is a meta data outside of [Content] value. The file name is in the [Name] column next to [Content]. To meet your requirement, we need to bring [Name] into the binary value in [Content], then it may be possible to be read into "Transform Sample File". However, I think this is not easy. Personally I'm not sure if this is possible currently.  

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @ryan1919 

Here is an alternative solution. You need to create a "Transform Sample File" function manually instead of using the function & queries generated automatically by Combining binary column [Content].

First connect to a CSV file using Text/CSV connector. Treat it as a sample file and transform it per your need. For example, below is my sample query Jan (sample file). 

Duplicate the sample query, then modify its code like below. I convert this query into a custom function with two parameters FileName and FolderPath. These paramters are used to connect to a CSV file and FileName is used to add a custom column which displays the file name. 

Then connect to the folder using Folder connector. On the Source step, remove other columns except [Name] and [Folder Path]. Invoke the custom function created earlier to add a column. Use [Name] and [Folder Path] to provide values for the parameters.  

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ryan1919]

... I forgot one other comment:

When I attempt to enter the Custom Column with the code from the mrexcel.com link:

Source = Folder.Files("C:\Path")[Name]

The only "name" that appears in the list is the Property Name.  There is no [Name] nor [FileName] that I am able to find.

Now, within my .csv files, there are columns that are titled "Name" and viewing the previous code, when PowerBI Promoted Headers it would then take "Name" from the .csv file, and now make it an actual Column Header.  Perhaps that is breaking the link between [Name] as referencing the actual file name *.csv, and is now causing the issue?

[ryan1919]

Hey @ShirinArshadnia 

Thank you for your reply, much appreciated.

To which "gear" are you referring to?  I have attached a screenshot of more information; there is a gear in the upper left toolbar; and there are many gears on the right hand side.

I have also shown the advanced editor, and pasted the same code below:

let
Source = Csv.Document(Parameter1,[Delimiter=",", Columns=29, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Municipality"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Street", "Property Street"}}),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Renamed Columns", "Text Between Delimiters", each Text.BetweenDelimiters([Fulladdress], ",", ","), type text),
#"Trimmed Text" = Table.TransformColumns(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", Text.Trim, type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Trimmed Text",{{"Text Between Delimiters", "Property City"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Name", "Fulladdress", "Property Street", "Property City", "Categories", "Plus Code", "Price", "Note", "Amenities", "Hotel Class", "Phone", "Phones", "Claimed", "Email", "Social Medias", "Review Count", "Average Rating", "Review URL", "Google Maps URL", "Google Knowledge URL", "Latitude", "Longitude", "Website", "Domain", "Opening Hours", "Featured Image", "Cid", "Place Id", "Kgmid"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Fulladdress", "Property Full Address"}}),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Renamed Columns2", "Text After Delimiter", each Text.AfterDelimiter([Property Full Address], ",", 1), type text),
#"Trimmed Text1" = Table.TransformColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", Text.Trim, type text}}),
#"Extracted First Characters" = Table.TransformColumns(#"Trimmed Text1", {{"Text After Delimiter", each Text.Start(_, 2), type text}}),
#"Renamed Columns3" = Table.RenameColumns(#"Extracted First Characters",{{"Text After Delimiter", "Property State"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns3",{"Name", "Property Full Address", "Property Street", "Property City", "Property State", "Categories", "Plus Code", "Price", "Note", "Amenities", "Hotel Class", "Phone", "Phones", "Claimed", "Email", "Social Medias", "Review Count", "Average Rating", "Review URL", "Google Maps URL", "Google Knowledge URL", "Latitude", "Longitude", "Website", "Domain", "Opening Hours", "Featured Image", "Cid", "Place Id", "Kgmid"}),
#"Inserted Last Characters" = Table.AddColumn(#"Reordered Columns1", "Last Characters", each Text.End([Property Full Address], 5), type text),
#"Renamed Columns4" = Table.RenameColumns(#"Inserted Last Characters",{{"Last Characters", "Property Zip Code"}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns4",{"Name", "Property Full Address", "Property Street", "Property City", "Property State", "Property Zip Code", "Categories", "Plus Code", "Price", "Note", "Amenities", "Hotel Class", "Phone", "Phones", "Claimed", "Email", "Social Medias", "Review Count", "Average Rating", "Review URL", "Google Maps URL", "Google Knowledge URL", "Latitude", "Longitude", "Website", "Domain", "Opening Hours", "Featured Image", "Cid", "Place Id", "Kgmid"}),
#"Added Index" = Table.AddIndexColumn(#"Reordered Columns2", "Index", 1, 1, Int64.Type),
#"Reordered Columns3" = Table.ReorderColumns(#"Added Index",{"Index", "Name", "Property Full Address", "Property Street", "Property City", "Property State", "Property Zip Code", "Categories", "Plus Code", "Price", "Note", "Amenities", "Hotel Class", "Phone", "Phones", "Claimed", "Email", "Social Medias", "Review Count", "Average Rating", "Review URL", "Google Maps URL", "Google Knowledge URL", "Latitude", "Longitude", "Website", "Domain", "Opening Hours", "Featured Image", "Cid", "Place Id", "Kgmid"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns3",{{"Review Count", Int64.Type}, {"Average Rating", type number}, {"Latitude", type number}, {"Longitude", type number}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Opening Hours", "Featured Image", "Cid", "Place Id", "Kgmid", "Review URL", "Google Maps URL", "Google Knowledge URL", "Price", "Note", "Amenities", "Hotel Class"}),
#"Cleaned Text" = Table.TransformColumns(#"Removed Columns1",{{"Website", Text.Clean, type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Cleaned Text", "Custom", each if [Website] = "" then "No" else "Yes"),
#"Renamed Columns5" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Website: Yes-No"}}),
#"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns5", "Phone: Yes-No", each if [Phone] = "" then "No" else "Yes"),
#"Inserted Text Between Delimiters1" = Table.AddColumn(#"Added Conditional Column1", "Text Between Delimiters", each Text.BetweenDelimiters([Phone], "(", ")"), type text),
#"Renamed Columns6" = Table.RenameColumns(#"Inserted Text Between Delimiters1",{{"Text Between Delimiters", "Property Area Code"}, {"Name", "Property Name"}})
in
#"Renamed Columns6"

At the beginning of the code, one specifc line item shows:

Source = Csv.Document(Parameter1,[Delimiter=",", Columns=29, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),

This looks different from the mrexcel.com link to similar code:

Source = Folder.Files("C:\Path")[Name]

Is my "Source = Csv.Document" causing errors, when it should be showing "Source = Folder.Files"?

Ryan

[ShirinArshadnia]

Hi @ryan1919 

 If I get your Idea correctly you can use this follow up:

Click the gear and make sure the Source Name column is visible. This is the file name.

At that point, your final query will have the file name imported, and you can manipulate it like any other column.

By the way for more help use this link:

https://www.mrexcel.com/board/threads/power-query-import-from-folder-csv-file-and-get-file-name.9462...