New data file failing in query import

[jillmhendrix]

I am self taught and do not have a coding background. I generally get Power BI, but I have a hard time wrapping my head around Power Query. 

I have a folder where I drop in monthly data. The last two months of files seem to be failing to import. When I open them manually I don't see any differences from files that are working. Same columns, same "export" sheet name.

Any help/advice is appreciated.

The error says: "An error occurred in the ‘Transform File (5)’ query. Expression.Error: The field 'Kind' of the record wasn't found.
Details:
Name=export
Data=[Table]"

The referenced Transform File (5) query code is:
let
Source = Excel.Workbook(Parameter5, null, true),
Sheets = Table.SelectRows(Source, each [Kind] = "Sheet" and [Hidden] <> true),
Navigation = Sheets{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Number of Order Status Requests", Int64.Type}, {"Month", type date}})
in
#"Changed Type"

Parameter5 code says: #"Sample File (5)" meta [IsParameterQuery=true, BinaryIdentifier=#"Sample File (5)", Type="Binary", IsParameterQueryRequired=true]

And Sample File (5) code says 
let
Source = Folder.Files("C:\Users\jhendrix\OneDrive - MVB GmbH\MVB-US - General\Power BI\Order Status Query Data"),
#"Filtered Excel Files" = Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$")),
#"Sorted Rows" = Table.Sort(#"Filtered Excel Files",{{"Date modified", Order.Descending}}),
Navigation1 = Source{0}[Content]
in
Navigation1

[v-echaithra]

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.

[jillmhendrix · Accepted solution]

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!

[v-echaithra]

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.

[jillmhendrix]

Issue temporarily resolved and will try user advice for a long-term solution when I have time.

[vojtechsima]

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])

[jillmhendrix]

Thanks - I do find the multiple queries confusing so I will try this. Since I'll have to be rewriting things anyway, do you recommend switching the file imports from .xlsx to .csv at the same time? I have the ability to export as .csv but had been using .xlsx since easier for me to open the files and double-check things in them.

[vojtechsima]

Hey, @jillmhendrix, it depends on the size of your data, but it doesn't really matter.

CSVs are more universal; I would stick with them, but if you have already .xlsx, it's okay.

[lbendlin]

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.

[jillmhendrix]

Thanks - feel free to post your blog entry too.

[lbendlin]

Here's my take:  Streamlined process for appending multiple files w... - Microsoft Fabric Community

[ralf_anton]

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.