Power Query Extract Files Function, please

[DrDebit]

In previous days, I imported a folder of csv files into Power Query and then used the Excel.Workbook function to extract the data and to promote headers. But all of a sudden it doesn't work anymore. Did they change it?

Anyway...What is the function that I can use to get the data and promote headers for csv files and/or tab delimited files. I am trying to keep the name of the file with the data. If I just download the data, the file name disappears. Thank you!!

 

[sandy666]

try

let
    Source = Folder.Files("pthtothefile"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from New folder", each #"Transform File from New folder"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from New folder"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from New folder", Table.ColumnNames(#"Transform File from New folder"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type date}, {"ID", type text}, {"Name", type text}, {"Company", type text}, {"Country", type text}, {"City", type text}, {"Sales", Int64.Type}, {"Text", type date}})
in
    #"Changed Type"

From Folder - Edit - Expand Binary

 

[DrDebit]

try

let
    Source = Folder.Files("pthtothefile"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from New folder", each #"Transform File from New folder"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from New folder"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from New folder", Table.ColumnNames(#"Transform File from New folder"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type date}, {"ID", type text}, {"Name", type text}, {"Company", type text}, {"Country", type text}, {"City", type text}, {"Sales", Int64.Type}, {"Text", type date}})
in
    #"Changed Type"

From Folder - Edit - Expand Binary

 

[DrDebit]

First, thank you so much!!
I tried this and am getting an error about 'Sample File' wasn't recognized. I put in one of my file names and that wasn't recognized either.

Thank you!

 

[sandy666]

M-code is an example to show how it works
you cannot use file.csv on the path because it is designed to recognise folder not file
my fault, I forgot s letter on the end of path ?

Just do: From Folder -...- Edit - Expand Binary column
you should see something like this, eg.: Source = Folder.Files("C:\MyFolder"),

 

[DrDebit]

Yes, thank you. I got the first line right with the source. The error occurs later on...

...but a more basic question, please...does Excel.Workbook not work with csv or tab delimited files? I thought that it used to.

Thank you!

 

[sandy666]

Be sure your csv files has the same structure

 

[DrDebit]

You have been sol helpful...thank you!!!!

Yes...but when I Expand content with binary, I lose all of the other data. I am trying to keep the file name (city, for example), so I use Excel.Workbook...I get the following error:

 

[sandy666]

csv is a csv not txt
I don't know what you have there, maybe mixed files
and you don't loose any data unless your files have different structure so your data will be in a different columns

this is not a way Click&Go, you need to know kind of data and structure

as you can see on my picture I did't loose any data on the end

 

[DrDebit]

You have been so generous with your time and trying to help me.

Even with csv files, I am getting the same error: