Forum Discussion

jisaenz's avatar
jisaenz
Helper I
2 years ago
Solved

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...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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}})

 

jisaenz's avatar
jisaenz
Helper I
2 years ago

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

Details
Reason = Expression.Error
  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Provide whole query please.

    • jisaenz's avatar
      jisaenz
      Helper I
      2 years ago
      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
      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        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