Historical Data wont combine with New data

[Craigc3814]

I am trying to bring some historical invoices into an invoicing tool that I built. I had the contractor send us all the historical files I created a new query just for those because it was missing some of the columns that our current data has. I got all the historical files into one spreadsheet and saved my work. I then copied the table, pasted it as values only and made it look like the current invoice data we get. When I drop it into my network folder I get this error

Expression.Error: The key didn't match any rows in the table.

Here is the query, I tried reading through other forums and taking others advice but it never seemed to be advice when combining multiple worksheets. I also understand that a new version of PQ exists to where I dont have to create the fnGetContents step but I have not had my version of excel updated yet.



let
Source = Folder.Files("W:\DATA\Operations\Procurement\Utility Locating Services\811 Invoices"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each fnGetContents([Folder Path]&[Name])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"request_no", "CDC", "type", "county", "township", "contractor", "address", "sortfullname", "workcode_id", "cost", "util_id", "transmit_date", "transmit_time", "dateofwork", "starttime", "endtime", "sba_desc", "workfor", "Caller", "WorkType", "DueDate", "PYPN_flag", "invoice_no", "dateofinv", "district", "Sales_Tax_City", "Sales_Tax_State"}, {"request_no", "CDC", "type", "county", "township", "contractor", "address", "sortfullname", "workcode_id", "cost", "util_id", "transmit_date", "transmit_time", "dateofwork", "starttime", "endtime", "sba_desc", "workfor", "Caller", "WorkType", "DueDate", "PYPN_flag", "invoice_no", "dateofinv", "district", "Sales_Tax_City", "Sales_Tax_State"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"transmit_date", type date}, {"dateofwork", type date}, {"DueDate", type date}, {"dateofinv", type date}, {"starttime", type time}, {"endtime", type time}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Work Date - Transmit", each [dateofwork]-[transmit_date]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Work Date - Transmit", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "Work On Time", each if[DueDate]<[dateofwork] then "Late" else null)
in
#"Added Custom2"

 

[Matt Allington]

When I drop it into my network folder I get this error

Expression.Error: The key didn't match any rows in the table

can you be more specific. When did the error occur - what triggered the error. I assume it wasn't the paste event in windows explorer

 

[Craigc3814]

the error is triggered by dropping the new file into the network folder. It gets thrown on the step combining excel files from the folder or the FnGetGetContents. Once I drop the new file in I hit refresh, the error happens. If I remove that one file, then refresh the error goes away.

 

[Matt Allington]

Sorry for being pedantic. Ok, the error is caused by the refresh when the new file is already in the folder.

have you opened the new file and a file that works side by side to compare them? Check that the sheet names are the same too

 

[Craigc3814]

It was the sheet name!!!! I would have never thought to look for that. THANK YOU!!!!

 

[Matt Allington]

I've been caught before too, that's how I know to check