New data file failing in query import

Power Query is case sensitive. Make sure all the sheets have exactly the same name across all files. (also look for trailing spaces)

Note: While the "Combine Binaries"  feature is ok for beginners, at some point you will want to clean that up and write your own multi-file ingestor.  I have a blog entry for that if interested.

Hey, jillmhendrix  I would drop the automatic combiner and write one yourself, it's way less messy and you have more control around it + you learn something new, it's not very hard.

You can just click steps for one of your files, remove steps with navigation and keep first step as the binary content, wrap the whole thing as function and that's it.

Then in your actual Query, do the navigation, get your files, and add new column where you invoke the function with the content (or transform existing content column).

Here's blog about it:

https://www.vojtechsima.com/post/combine-files-quickly-in-power-query

If it's a SharePoint, use a connector like this:

= SharePoint.Contents("https://tenant.sharepoint.com/sites/siteName", [ApiVersion=15])

Hallo,

sollte Navigation1 in Beispieldatei(5) nicht:

Navigation1 = #"Sorted Rows"{0}[Content]

 lauten?

Mit folgendem Beispielcode kannst Du alle Exceldateien eines Ordners einlesen, deren Datenquelle von der Art Kind = Sheet sind. Für die Pafadangabe benötigst Du noch eine Tabelle tblPfad:

Ansonsten kannst Du alles mit dieser einen Abfrage erschlagen. Voraussetzung ist natürlich, die Datenstruktur ist identisch.

let
    Pfad =  Excel.CurrentWorkbook(){[Name="tblParameter"]}[Content][Pfad]{0},
//Schritt 1: Den Inhalt des Ordners einlesen. 
    Quelle = Folder.Files(Pfad),

//Schritt 2 (optional): Nach Dateitypen (oder andere Dateiattributen) filtern. Groß- und Kleinschreibung dabei ignorieren    
    NurExceldateien = Table.SelectRows(Quelle, each Text.Contains(Text.Lower([Extension]) , ".xls" ) and not Text.StartsWith([Name], "~$")),
 
//Schritt 3: Die Liste der Spalte Content in eine Tabelle konvertieren   
    ContentZuTabelle = Table.FromList(NurExceldateien[Content], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    AddIndex = Table.AddIndexColumn(ContentZuTabelle, "Index", 0, 1, Int64.Type),
    AddColDateinamen = Table.AddColumn(AddIndex, "Pfad", each NurExceldateien[Folder Path]{[Index]} & NurExceldateien[Name]{[Index]}),
    Dateien = Table.AddColumn(AddColDateinamen, "Datei", each  Quelle[Name]{[Index]}, type text),
    AddColAlleQuelltables = Table.AddColumn(Dateien, "AlleQuellen", each Excel.Workbook(File.Contents([Pfad] ),null,true)),
    #"Erweiterte AlleQuellen" = Table.ExpandTableColumn(AddColAlleQuelltables, "AlleQuellen", {"Name", "Data", "Kind"}, {"AlleQuellen.Name", "AlleQuellen.Data", "AlleQuellen.Kind"}),
    NurSheets = Table.SelectRows(#"Erweiterte AlleQuellen", each ([AlleQuellen.Kind] = "Sheet")),
//Hier bei Bedarf noch einen Filter verwenden   
    DelCols = Table.RemoveColumns(NurSheets,{"Index","Pfad","AlleQuellen.Kind"}),
    NeueSpaltennamen = Record.ToList( DelCols[AlleQuellen.Data]{0}{0}),
    AlteSpaltennamen = Table.ColumnNames(DelCols[AlleQuellen.Data]{0}),
    ExpandDatas = Table.ExpandTableColumn(DelCols,"AlleQuellen.Data",AlteSpaltennamen,NeueSpaltennamen),
    #"Geänderter Typ" = Table.TransformColumnTypes(ExpandDatas,{{"ID", Int64.Type}}),
    #"Entfernte Fehler" = Table.RemoveRowsWithErrors(#"Geänderter Typ", {"ID"}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Entfernte Fehler",{"Column1", "ID"})
in
    #"Entfernte Spalten"

Die Datentransformation selbst musst Du selbstverständlich an Deine Bedürfnisse anpassen.

Hi jillmhendrix ,

Thank you lbendlin , vojtechsima , ralf_anton  for your inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you.

So what has temporarily solved the problem for me was simply to open the files and resave them as Excel workbooks. Given this, I think when I have time to play around with writing my own combiner I will try it with .csv versions in the hopes that those are less finicky than the .xlsx files our system is producing. Thanks, everyone for your advice!

Hi jillmhendrix ,

Great to hear you found a workaround, that’s a good step forward.

If anything else comes up as you refine things, feel free to reach out anytime.

Thank you.