Forum Discussion
Adding A custom Column to the Transform file
I am working in Data Factory with Power Query using a Folder Connector I Created a Custom Coulmn extracting information form the Name of the file and I would like to be included in the Trasnform file What I need to do the name of the Custom Column is Week
Use this:
letSource = Folder.Files("C:\Users\jisae\OneDrive\Documents\jj-Analytics\Data"),#"Added custom" = Table.TransformColumnTypes(Table.AddColumn(Source, "Week", each Text.Middle([Name], 12, 10)), {{"Week", type date}}),#"Filtered Rows" = Table.SelectRows(#"Added custom", each Date.IsInPreviousNDays([Week], 90)),Custom = Table.TransformColumns(#"Filtered Rows", {{"Content", each Excel.Workbook(_, true), type table}})inCustom
10 Replies
Hi, why do you need it in Transform File? You have filename in main query by default.
I need it to have unique keys in the transfor filw if not the combine will be genrating duplicates the unique key that I want is Week, Site, Part, Period. Site Part Period exists in the excel files.
You can check for duplicates in master file before Invokde Custom Function step or even after. Could you share some example what do you want to achieve?
I created the week custum coulmn to allow me to filter the files for the previous 90 days and is working form here I would like to add the week as coulmn to the transform file. Them in the transform file I would like to clean the data in the first file, change the types and Unpivot some coulms previous to combine them
I recommend add this as new step just after Filtered Rows step.
This will transform [Content] from binary to table.
If your tables don't have columns in 1st row, change true to null.
After this you can delete all steps below and also whole Transform File group.
Then just expand [Content] table.
= Table.TransformColumns(#"Filtered Rows", {{"Content", each Excel.Workbook(_, true), type table}})
