Craigc3814
Board Regular
- Joined
- Mar 7, 2016
- Messages
- 217
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"
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"