problems with power query; newbie|Power Query|Excel Forum|My Online Training Hub

Indeed, when you combine files from a folder as you describe, PQ creates one table, listing the content of all files one after the other. Let's assume that the first column is called "Source.Name" and the other "Column1"

The next thing you need to do is group the table by Source.Name with 'Operation' set to 'All rows'.

By default, PQ groups into a column of tables, where each table contains two columns. One for the Source name and one for the data. Expanding that column of tables brings you back to square 1. No good!

You need to get into the M-code of the grouping step and change this:

= Table.Group(#"Expanded Table Column1", {"Source.Name"}, {{"Grp", each _ , type table [Source.Name=text, Column1=text]}})

to this:

= Table.Group(#"Expanded Table Column1", {"Source.Name"}, {"Grp", each _ [Column1]})

So, delete everything that I marked red in the original step.

This will group the data into a column of lists containing only the data from each file. Now you need to add another piece of code to create a proper table from it all.

I created some files called textfileA.txt, textfileB.txt etc. in a folder on my C:\ drive. The entire code for the final query looked like below and you should recognize the first 6 steps as these are created automatically when you connect to files in a folder and filter only the files you need.

let
    Source = Folder.Files("C:\-----------"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "textfile")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", 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"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    Expand = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", {"Column1"}, {"Column1"}),
    Grouped = Table.Group(Expand, {"Source.Name"}, {"Grp", each _ [Column1]}),
    MakeTable = Table.FromColumns (Grouped[Grp], Grouped[Source.Name])
in
    MakeTable

See if you can get this to work in your own file. If not, come back here.

Okay! Let's give it another try and let's combine the first part of your query with the last part of mine. As you can see, English PQ calls the function (fx) 'Transform File', whereas German PQ seems to call it after the main folder that it connects to. In your case 'Datei aus c:\ transformieren'. That's why my query broke when it could not find the function "Transform File". So let's combine the first part of your query and the latter part of mine (in bold). Then the query looks like below. I tested it on a some text files in a folder named similar to yours. And it work on my end.

let
    Quelle = Folder.Files("C:\Users\kirsch\Desktop\text\"),
    #"Gefilterte ausgeblendete Dateien1" = Table.SelectRows(Quelle, each [Attributes]?[Hidden]? <> true),
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(#"Gefilterte ausgeblendete Dateien1", "Datei aus c:\ transformieren", each #"Datei aus c:\ transformieren"([Content])),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Benutzerdefinierte Funktion aufrufen1", {"Name", "Source.Name"}),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Umbenannte Spalten1", {"Source.Name", "Datei aus c:\ transformieren"}),
    Expand = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei aus c:\ transformieren", Table.ColumnNames(#"Datei aus c:\ transformieren"(Beispieldatei))),
    Grouped = Table.Group(Expand, {"Source.Name"}, {"Grp", each _ [Column1]}),
    MakeTable = Table.FromColumns (Grouped[Grp], Grouped[Source.Name])
in
    MakeTable