Import several source files in several sheets in Excel

[Manuel123]

Dear Community,

I hope that you can help me with the following:

I import several files from a folder based on their date, name and other metrics. That does work like a charm (Thank you @v-yiruan-msft for your help to get this done). So now I'm at the point that I have e.g. 20 lines with several files:

If I would now expand the conent, everything would be in one table and I understand, that this could than only be imported in Excel in one sheet. However, I would like to import the content of File 1 in sheet 1, File 2 in sheet 2 and so on. Is this possible?

My import code so far (ChosenGAAP, ChosenScenario and FolderPath are Parameters from an ExcelTable):

let
    Source = Folder.Files(FolderPath),
    #"Spalte nach Trennzeichen teilen" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3", "Name.4"}),
    #"Ersetzter Wert" = Table.ReplaceValue(#"Spalte nach Trennzeichen teilen",".csv","",Replacer.ReplaceText,{"Name.4"}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Ersetzter Wert",{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Entfernte Spalten",{{"Name.1", type date}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"Name.1", "ExportDate"}, {"Name.2", "ProjectName"}, {"Name.3", "Scenario"}, {"Name.4", "GAAP"}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Umbenannte Spalten", "FileID", each [ProjectName]&[Scenario]&[GAAP]),
    #"Grouped Rows" =  Table.Group(#"Hinzugefügte benutzerdefinierte Spalte", {"FileID"},{{"Latest Record", each Table.FirstN(Table.Sort(_, {{"ExportDate", Order.Descending}}),1), type table [Content=binary, ProjectName=text, Scenario=text, ExportDate created=nullable datetime, GAAP=text]}}),
    #"Expanded maxdate" = Table.ExpandTableColumn(#"Grouped Rows", "Latest Record", {"Content", "ProjectName", "Scenario","GAAP","ExportDate"}, {"Content", "ProjectName", "Scenario","GAAP","ExportDate"}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Expanded maxdate", each ([Scenario] = ChosenScenario) and ([GAAP] =ChosenGAAP))
in
    #"Gefilterte Zeilen"

I hope that you can help me.

Best regards

Manuel

[watkinnc · Accepted solution]

Easier than you might think! You can leave the rows of content as is, then right click on your query, and select "Reference".

Now you have a new query that references those content rows. Now you can right click on the File ID value that you'd like to load to sheet1, and select "Equals".  Now your reference query has just the table you want to load. You can again right click on the original query and make another reference query, and filter to the Flie ID for the second table you want to load. 
If you need it to be dynamic, you can instead make a blank query like:

= QueryName{0}

This will give you just the first row. QueryName{1} will give you the second row, and so on.

--Nate

[Manuel123]

Dear Nate,

thank you very much, that was exactly what I was looking for.

BR
Manuel