Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jisaenz
Helper I
Helper I

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

1 ACCEPTED SOLUTION

Use this:

 

let
  Source = 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}})
in
  Custom

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

10 REPLIES 10
dufoq3
Super User
Super User

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.

 

dufoq3_0-1713625525454.png

= Table.TransformColumns(#"Filtered Rows", {{"Content", each Excel.Workbook(_, true), type table}})

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

fails after insreting the new step 
The import Filtered Rows matches no exports. Did you miss a module reference?

Details
Reason = Expression.Error

Provide whole query please.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

let
  Source = 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}})
in
  Custom

Use this:

 

let
  Source = 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}})
in
  Custom

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Finally I found to add the week column to the data table not the Transform but works

Table.SelectColumns(#"Removed columns 1", {"Week", "Transform file"}) only addig Week. 
jisaenz
Helper I
Helper I

jisaenz_0-1713623722913.png

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 

dufoq3
Super User
Super User

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?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

jisaenz
Helper I
Helper I

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.

dufoq3
Super User
Super User

Hi, why do you need it in Transform File? You have filename in main query by default.

 

dufoq3_0-1713620803144.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.