cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jay1031
Frequent Visitor

Expression Error Related to missing column on a table that Power Query Can't find

Hi Power BI Community,

 

I need your experience and knowledege in order to solve this issue I'm experiencing.

Below is the entire code from the advanced editor in Power Query alongside the error prompt. I can't seem to figure out how to fix this error and would appreciate your help in solving this problem and take it awat as a learning experience to continue growing my Power BI skillset.

 

Jay1031_0-1623250623816.png

 

 

let
Source = Folder.Files("C:\Users\jrodriguez\OneDrive - Removed for Privacy,
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Order Created Date", type date}, {"Order Created Time", type time}, {"Date Order Changed", type date}, {"Order Number", Int64.Type}, {"Order Id", Int64.Type}, {"Marketplace", type text}, {"Marketplace PO#", Int64.Type}, {"Reference Order #", type any}, {"Customer Order #", type text}, {"Lot ID", type text}, {"Has Manifested Pallets", type logical}, {"OrderTypeId", Int64.Type}, {"Package Type", type text}, {"Order Quantity", Int64.Type}, {"Initial Product Quantity", Int64.Type}, {"Order Sale Price", type number}, {"Order Sale Price Currency", type text}, {"Order Discount Amount", type number}, {"Order Discount Amount Currency", type text}, {"Order Tax Amount", type number}, {"Order Tax Amount Currency", type text}, {"Order Shipping Cost", type number}, {"Order Shipping Cost Currency", type text}, {"Order Total", type number}, {"Order Total Currency", type text}, {"Order Payment Received", type number}, {"Order Payment Received Currency", type text}, {"Order Total Refunded", type number}, {"Order Total Refunded Currency", type text}, {"Converted Order Sale Price", type number}, {"Converted Order Sale Price Currency", type text}, {"Converted Order Discount Amount", type number}, {"Converted Order Discount Currency", type text}, {"Converted Order Tax Amount", type number}, {"Converted Order Tax Currency", type text}, {"Converted Order Shipping Cost", type number}, {"Converted Order Shipping Currency", type text}, {"Converted Order Total", type number}, {"Converted Order Total Currency", type text}, {"Converted Order Payment Received", type number}, {"Converted Order Payment Received Currency", type text}, {"Converted Order Total Refunded", type number}, {"Converted Order Total Refunded Currency", type text}, {"Exchange Rate", Int64.Type}, {"Initial Sale Price", type number}, {"Initial Sale Price Currency", type text}, {"Payment Type", type text}, {"Order Payment ID", type text}, {"Order Payment Received Date", type date}, {"Order ChargeBack", type any}, {"Routing Date", type any}, {"ShippingCarriers", type text}, {"CarrierOrderShippingCost", type number}, {"ShippingBundleId", Int64.Type}, {"TrackingNumber", type text}, {"Shipping Deadline", type any}, {"Date Shipment Prepared", type date}, {"Date Ready To Pick", type date}, {"Shiping Failsafe Completed", type date}, {"Order Closed Date", type date}, {"Order Closed By", type text}, {"Order Deleted Date", type logical}, {"OrderDeletedBy", type any}, {"SalesRepresentative", type text}, {"TotalPalletQuantity", Int64.Type}, {"TotalPalletWeight", Int64.Type}, {"Lot ID Pallet Count", Int64.Type}, {"WarehouseNames", type text}, {"RC", type text}, {"OrderCost", Int64.Type}, {"Back Bill", type logical}, {"CustomerID", Int64.Type}, {"Customer", type text}, {"FullName", type text}, {"Marketplace User ID", Int64.Type}, {"EmailAddress", type text}, {"StateCode", type text}, {"CountryCode", type text}, {"AddressLine1", type text}, {"AddressLine2", type text}, {"AddressLine3", type any}, {"City", type text}, {"ZipCode", Int64.Type}, {"Phone", type text}, {"RuleId", type any}, {"routingdate", type any}, {"shippingdeadline", type any}, {"DatePaymentReceived", type date}, {"DateOrderShipped", type date}, {"DateOrderClosed", type date}, {"readytopick", type date}, {"DirectShip", type logical}, {"Direct Ship BLs", type any}, {"BenchmarkOrBackBillAmount", type number}, {"VendorInvoiceTotal", type number}, {"Order Notes", type text}, {"Custom Carrier SCAC", type any}, {"Custom Carrier LoadID", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Order Number", type text}, {"Order Id", type text}, {"Marketplace PO#", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Reference Order #"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Customer Order #", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2","","Manual Sell ",Replacer.ReplaceValue,{"Marketplace"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"N/A",Replacer.ReplaceValue,{"Marketplace PO#"}),
#"Removed Columns2" = Table.RemoveColumns(#"Replaced Value1",{"Lot ID", "Has Manifested Pallets"}),
#"Replaced Value2" = Table.ReplaceValue(#"Removed Columns2",null,0,Replacer.ReplaceValue,{"Order Sale Price"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value2",{{"Order Sale Price", Currency.Type}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type3",{"Order Sale Price Currency"}),
#"Replaced Value3" = Table.ReplaceValue(#"Removed Columns3",null,0,Replacer.ReplaceValue,{"Order Discount Amount"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value3",{{"Order Discount Amount", Currency.Type}}),
#"Removed Columns4" = Table.RemoveColumns(#"Changed Type4",{"Order Discount Amount Currency"}),
#"Replaced Value4" = Table.ReplaceValue(#"Removed Columns4",null,0,Replacer.ReplaceValue,{"Order Tax Amount"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Replaced Value4",{{"Order Tax Amount", Currency.Type}}),
#"Removed Columns5" = Table.RemoveColumns(#"Changed Type5",{"Order Tax Amount Currency"}),
#"Replaced Value5" = Table.ReplaceValue(#"Removed Columns5",null,0,Replacer.ReplaceValue,{"Order Shipping Cost"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Replaced Value5",{{"Order Shipping Cost", Currency.Type}}),
#"Removed Columns6" = Table.RemoveColumns(#"Changed Type6",{"Order Shipping Cost Currency"}),
#"Changed Type7" = Table.TransformColumnTypes(#"Removed Columns6",{{"Order Total", Currency.Type}}),
#"Replaced Value6" = Table.ReplaceValue(#"Changed Type7",null,0,Replacer.ReplaceValue,{"Order Total"}),
#"Changed Type8" = Table.TransformColumnTypes(#"Replaced Value6",{{"Order Total", Currency.Type}}),
#"Removed Columns7" = Table.RemoveColumns(#"Changed Type8",{"Order Total Currency"}),
#"Changed Type9" = Table.TransformColumnTypes(#"Removed Columns7",{{"Order Payment Received", Currency.Type}}),
#"Removed Columns8" = Table.RemoveColumns(#"Changed Type9",{"Order Payment Received Currency"}),
#"Changed Type10" = Table.TransformColumnTypes(#"Removed Columns8",{{"Order Total Refunded", Currency.Type}}),
#"Removed Columns9" = Table.RemoveColumns(#"Changed Type10",{"Order Total Refunded Currency"}),
#"Changed Type11" = Table.TransformColumnTypes(#"Removed Columns9",{{"Converted Order Sale Price", Currency.Type}}),
#"Replaced Value7" = Table.ReplaceValue(#"Changed Type11",null,0,Replacer.ReplaceValue,{"Converted Order Sale Price"}),
#"Changed Type12" = Table.TransformColumnTypes(#"Replaced Value7",{{"Converted Order Sale Price", Currency.Type}}),
#"Removed Columns10" = Table.RemoveColumns(#"Changed Type12",{"Converted Order Sale Price Currency"}),
#"Replaced Value8" = Table.ReplaceValue(#"Removed Columns10",null,0,Replacer.ReplaceValue,{"Converted Order Discount Amount"}),
#"Changed Type13" = Table.TransformColumnTypes(#"Replaced Value8",{{"Converted Order Discount Amount", Currency.Type}}),
#"Removed Columns11" = Table.RemoveColumns(#"Changed Type13",{"Converted Order Discount Currency"}),
#"Changed Type14" = Table.TransformColumnTypes(#"Removed Columns11",{{"Converted Order Tax Amount", Currency.Type}}),
#"Replaced Value9" = Table.ReplaceValue(#"Changed Type14",null,0,Replacer.ReplaceValue,{"Converted Order Tax Amount"}),
#"Changed Type15" = Table.TransformColumnTypes(#"Replaced Value9",{{"Converted Order Tax Amount", Currency.Type}}),
#"Removed Columns12" = Table.RemoveColumns(#"Changed Type15",{"Converted Order Tax Currency"}),
#"Changed Type16" = Table.TransformColumnTypes(#"Removed Columns12",{{"Converted Order Shipping Cost", Currency.Type}}),
#"Replaced Value10" = Table.ReplaceValue(#"Changed Type16",null,0,Replacer.ReplaceValue,{"Converted Order Shipping Cost"}),
#"Changed Type17" = Table.TransformColumnTypes(#"Replaced Value10",{{"Converted Order Shipping Cost", Currency.Type}}),
#"Removed Columns13" = Table.RemoveColumns(#"Changed Type17",{"Converted Order Shipping Currency"}),
#"Changed Type18" = Table.TransformColumnTypes(#"Removed Columns13",{{"Converted Order Total", Currency.Type}}),
#"Replaced Value11" = Table.ReplaceValue(#"Changed Type18",null,0,Replacer.ReplaceValue,{"Converted Order Total"}),
#"Changed Type19" = Table.TransformColumnTypes(#"Replaced Value11",{{"Converted Order Total", Currency.Type}}),
#"Removed Columns14" = Table.RemoveColumns(#"Changed Type19",{"Converted Order Total Currency"}),
#"Changed Type20" = Table.TransformColumnTypes(#"Removed Columns14",{{"Converted Order Payment Received", Currency.Type}}),
#"Removed Columns15" = Table.RemoveColumns(#"Changed Type20",{"Converted Order Payment Received Currency"}),
#"Changed Type21" = Table.TransformColumnTypes(#"Removed Columns15",{{"Converted Order Total Refunded", Currency.Type}}),
#"Removed Columns16" = Table.RemoveColumns(#"Changed Type21",{"Converted Order Total Refunded Currency", "Exchange Rate"}),
#"Replaced Value12" = Table.ReplaceValue(#"Removed Columns16",null,0,Replacer.ReplaceValue,{"Initial Sale Price"}),
#"Changed Type22" = Table.TransformColumnTypes(#"Replaced Value12",{{"Initial Sale Price", Currency.Type}}),
#"Removed Columns17" = Table.RemoveColumns(#"Changed Type22",{"Initial Sale Price Currency", "Order ChargeBack", "Routing Date"}),
#"Replaced Value13" = Table.ReplaceValue(#"Removed Columns17","","Unknown",Replacer.ReplaceValue,{"ShippingCarriers"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13",null,0,Replacer.ReplaceValue,{"CarrierOrderShippingCost"}),
#"Changed Type23" = Table.TransformColumnTypes(#"Replaced Value14",{{"CarrierOrderShippingCost", Currency.Type}, {"ShippingBundleId", type text}}),
#"Replaced Value15" = Table.ReplaceValue(#"Changed Type23",null,"N/A",Replacer.ReplaceValue,{"ShippingBundleId"}),
#"Changed Type24" = Table.TransformColumnTypes(#"Replaced Value15",{{"TrackingNumber", type text}}),
#"Replaced Value16" = Table.ReplaceValue(#"Changed Type24","","Not Available ",Replacer.ReplaceValue,{"TrackingNumber"}),
#"Removed Columns18" = Table.RemoveColumns(#"Replaced Value16",{"Shipping Deadline"}),
#"Replaced Value17" = Table.ReplaceValue(#"Removed Columns18","","Not Available",Replacer.ReplaceValue,{"Order Closed By"}),
#"Removed Columns19" = Table.RemoveColumns(#"Replaced Value17",{"OrderDeletedBy"}),
#"Replaced Value18" = Table.ReplaceValue(#"Removed Columns19","","Not Available",Replacer.ReplaceValue,{"SalesRepresentative"}),
#"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18",null,0,Replacer.ReplaceValue,{"TotalPalletQuantity"}),
#"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19",null,0,Replacer.ReplaceValue,{"TotalPalletWeight"}),
#"Removed Columns20" = Table.RemoveColumns(#"Replaced Value20",{"Lot ID Pallet Count", "RC", "OrderCost"}),
#"Changed Type25" = Table.TransformColumnTypes(#"Removed Columns20",{{"CustomerID", type text}}),
#"Replaced Value21" = Table.ReplaceValue(#"Changed Type25","","Not Available",Replacer.ReplaceValue,{"FullName"}),
#"Removed Columns21" = Table.RemoveColumns(#"Replaced Value21",{"Marketplace User ID", "AddressLine2", "AddressLine3"}),
#"Changed Type26" = Table.TransformColumnTypes(#"Removed Columns21",{{"ZipCode", type text}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type26", {"ZipCode"}),
#"Replaced Value22" = Table.ReplaceValue(#"Removed Errors","","Not Available",Replacer.ReplaceValue,{"Phone"}),
#"Removed Columns22" = Table.RemoveColumns(#"Replaced Value22",{"RuleId", "routingdate", "shippingdeadline", "DirectShip", "Direct Ship BLs"}),
#"Changed Type27" = Table.TransformColumnTypes(#"Removed Columns22",{{"BenchmarkOrBackBillAmount", Currency.Type}}),
#"Replaced Value23" = Table.ReplaceValue(#"Changed Type27",null,0,Replacer.ReplaceValue,{"BenchmarkOrBackBillAmount"}),
#"Changed Type28" = Table.TransformColumnTypes(#"Replaced Value23",{{"BenchmarkOrBackBillAmount", Currency.Type}}),
#"Replaced Value24" = Table.ReplaceValue(#"Changed Type28",null,0,Replacer.ReplaceValue,{"VendorInvoiceTotal"}),
#"Changed Type29" = Table.TransformColumnTypes(#"Replaced Value24",{{"VendorInvoiceTotal", Currency.Type}}),
#"Removed Columns23" = Table.RemoveColumns(#"Changed Type29",{"Order Notes", "Custom Carrier SCAC", "Custom Carrier LoadID"}),
#"Replaced Value25" = Table.ReplaceValue(#"Removed Columns23","","Not Available",Replacer.ReplaceValue,{"Payment Type"})
in
#"Replaced Value25"

6 REPLIES 6
v-luwang-msft
Community Support
Community Support

Hi @Jay1031 ,

Has your question solved?Please check if the Order Created Date column exists.

 

Best Regards

Lucien

Hi Lucien,

 

No not really, I went ahead and deleted the file and restarted all over.

HotChilli
Super User II
Super User II

Try not to overthink it.  Click on the query step for #"Changed Type" -> does it give an error regarding the "order created date" column? Is there an "order created date" column?

 

I wasn't able to locate the error in my step and deleted it and started over. I need to get a better understanding of these error types and why they happen. The prompts aren't very intuitive.

 

Thank you!

HotChilli
Super User II
Super User II

Assuming the error comes from the "Changed Type" step, it's looking for a column "Order Created Date" to change it to type date.  It can't find a column with that name in "Expanded Table Column1".  

Has this code been edited from a previously working version?

Hi @HotChilli 

 

Thank you for your reply! I haven't edited the code. The only thing I've done is duplicate the data table to create lookup tables from the original table. I have the missing column showing up in my calendar lookup table as well. All of the other tables now show an exclamation mark due to this error. I'm lost on how to fix it.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors