cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
doctornick0 Frequent Visitor
Frequent Visitor

Import CSV retain path and file name

Hi, everybody!

 

I am trying to import a single CSV file via Power Query and retain the filename and path data, but I'm having a tough time adding a step that gets me that information.  Because the path gives me the date information from which the snapshot was taken, I need to pull this into a column in the table so that I can tag each record as belonging to that particular snapshot file.

 

let
    Source = Csv.Document(File.Contents("H:\Box\DailyPace\Daily Pace Snapshots\2019\February 2019\Pace Report Data_110319.csv"),[Delimiter=",", Columns=17, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Start Date", type date}, {"Owner Name", type text}, {"Property", type text}, {"Actual Revenue Total", type number}, {"Actual Revenue Subtotal", type number}})
in
    #"Changed Type"

Please let me know if there is a step I could add / replace to my M code to allow me to extract the path into a column.

 

Thank you for your help,

 

-doctornick0

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Import CSV retain path and file name

Hi @doctornick0 ,

 

We can get the file from Folder to work on it. M code for your reference.

 

let
    Source = Folder.Files("D:\Case\20190313\test"),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from test (2)", each #"Transform File from test (2)"([Content])),
    #"Expanded Transform File from test (2)" = Table.ExpandTableColumn(#"Invoke Custom Function1", "Transform File from test (2)", {"col1", "col2", "col3"}, {"Transform File from test (2).col1", "Transform File from test (2).col2", "Transform File from test (2).col3"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Transform File from test (2)",{"Content", "Attributes"})
in
    #"Removed Columns1"

fol.PNG

 

Capture.PNG

 

Regards,

Frank

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Import CSV retain path and file name

Hi @doctornick0 ,

 

We can get the file from Folder to work on it. M code for your reference.

 

let
    Source = Folder.Files("D:\Case\20190313\test"),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from test (2)", each #"Transform File from test (2)"([Content])),
    #"Expanded Transform File from test (2)" = Table.ExpandTableColumn(#"Invoke Custom Function1", "Transform File from test (2)", {"col1", "col2", "col3"}, {"Transform File from test (2).col1", "Transform File from test (2).col2", "Transform File from test (2).col3"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Transform File from test (2)",{"Content", "Attributes"})
in
    #"Removed Columns1"

fol.PNG

 

Capture.PNG

 

Regards,

Frank

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

doctornick0 Frequent Visitor
Frequent Visitor

Re: Import CSV retain path and file name

Good call, I then just filtered the folder for the file I actually wanted, which actually works out well, I can pass the file name via code to the query in that case.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,910)