Keeping File Name as a Column

[JMSNYC]

Hi.

Could anyone help with the current issue:

I will need to keep the Name field as the first column of my uploaded table. But when I click on Binary, this info disappear.

I used in the past the "Combine" / Invoke functions, but can no longer use that, since it crashes the automated refresh in PowerBIEmbedded. I need to keep the code as simple as possible, so automated refresh does not crash.

Thank you for your help.

JM

[v-xuding-msft]

Hi @JMSNYC ,
Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xuding-msft · Accepted solution]

Hi @JMSNYC ,

 I created a sample which is combined excel files. 

New folder (2):
let
    Source = Folder.Files("D:\New folder (2)"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Source.Name", "Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Transform File"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns",{"Source.Name", "Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Source.Name", "Data"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.PromoteHeaders([Data])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Data"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Id", "Name", "DATE", "Key", "Id ", "sales"}, {"Custom.Id", "Custom.Name", "Custom.DATE", "Custom.Key", "Custom.Id ", "Custom.sales"})
in
    #"Expanded Custom1"

New folder (3):
let
    Source = Folder.Files("D:\New folder (2)"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Source.Name", "Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Transform File"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns",{"Source.Name", "Content"}),
    Content = #"Removed Other Columns"{0}[Content],
    #"Imported Excel" = Excel.Workbook(Content),
    Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet1_Sheet

There is a video you can learn from. And I attached my sample that you can download.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.