Reputation: 1
I have changed my data source and I am getting errors when trying to load the new data, "Column 'Type' in table 'Discrepancies and Reworks' cannot be found or may not be used in this expression." When I brought in the new dataset, I walked through every step in Power Query to exactly replicate the column names that were in the previous dataset. I have validated there are no spaces and the column name is exactly the same as before. I've also verified the column giving the error is identical to the column referenced in the measures. I have no errors in Power Query, but get the error when loading the data into the model.
I've found a few solutions but they are timely and seem extreme. If I comment out all measures and calculated columns, it will load. Also had some luck breaking all the links with the changed dataset then re-establishing them after the dataset loads; however, this option doesn't always work for all columns. Both these options are very inconvenient when it seems this should be a simple load.
After some research, I have also cleared my caches and cleared permissions; however, these have not helped solve the issue.
This has happened on any occasion where I've changed the dataset, even when all columns are identical to the previous dataset.
Any help is greatly appreciated! Thanks in advance!
Note: in this case, I had errors on multiple columns. After breaking all my links, this is the only one remaining. If there is an error in my methods, I imagine it applies to multiple columns, not just the "Type" column.
Advanced editor:
let
Source = Excel.Workbook(File.Contents("L:\Manufacturing Engineering\ME Working Folders\Lish Adsit\Projects\Active Projects\Engineering\Quality\Disc_reworks.xlsx"), null, true),
#"Discrepancies and Reworks - Ful_Sheet" = Source{[Item="Discrepancies and Reworks - Ful",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"Discrepancies and Reworks - Ful_Sheet",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Type", "DRNumber", "Part Number", "Part Description", "Prod. Code - Line", "Current Part Cost", "MEAssignee", "QEAssignee", "DEAssignee", "EEAssignee", "OEAssignee", "SQEAssignee", "MRBHold", "Scrap Estimated Value", "MESignature", "QESignature", "DESignature", "OESignature", "EESignature", "SQESignature", "Closed By Signature", "Closed Date", "Create Date", "mrblocation", "wherefound", "disposition", "quantityreject", "responsibility", "defectcode", "workcenter", "containmentchangepersondate", "Supplier Name"}),
#"Added Prod Code column" = Table.AddColumn(#"Removed Other Columns", "Product Code", each Text.Start([#"Prod. Code - Line"], 4 )),
#"Renamed Columns1" = Table.RenameColumns(#"Added Prod Code column",{{"wherefound", "Where Found"}, {"containmentchangepersondate", "Containment Date"}, {"disposition", "Disposition"}, {"quantityreject", "Quantity Rejected"}, {"DRNumber", "DR Number"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"DR Number", Int64.Type}, {"Part Number", type text}, {"Part Description", type text}, {"Prod. Code - Line", type text}, {"Current Part Cost", type number}, {"MEAssignee", type text}, {"QEAssignee", type text}, {"DEAssignee", type text}, {"EEAssignee", type any}, {"OEAssignee", type any}, {"SQEAssignee", type text}, {"MRBHold", type logical}, {"Scrap Estimated Value", type number}, {"MESignature", type text}, {"QESignature", type text}, {"DESignature", type text}, {"OESignature", type text}, {"EESignature", type text}, {"SQESignature", type text}, {"Closed By Signature", type text}, {"Closed Date", type date}, {"Create Date", type date}, {"Supplier Name", type text}, {"Product Code", Int64.Type}, {"mrblocation", type text}, {"Where Found", type text}, {"Disposition", type text}, {"Quantity Rejected", Int64.Type}, {"responsibility", type text}, {"defectcode", type text}, {"workcenter", type text}, {"Containment Date", type date}}),
#"Filtered Rows - Closed dates" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNYears([Closed Date], 2) or Date.IsInCurrentYear([Closed Date]) or [Closed Date] = null),
#"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows - Closed dates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"
Upvotes: 0
Views: 551
Reputation: 5202
Try removing your line:
#"Removed Top Rows" = Table.Skip(#"Discrepancies and Reworks - Ful_Sheet",1),
After you remove it, change the line after it from:
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
to:
#"Promoted Headers" = Table.PromoteHeaders(#"Discrepancies and Reworks - Ful_Sheet", [PromoteAllScalars=true]),
Your final code would look like this:
let
Source = Excel.Workbook(File.Contents("L:\Manufacturing Engineering\ME Working Folders\Lish Adsit\Projects\Active Projects\Engineering\Quality\Disc_reworks.xlsx"), null, true),
#"Discrepancies and Reworks - Ful_Sheet" = Source{[Item="Discrepancies and Reworks - Ful",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Discrepancies and Reworks - Ful_Sheet", [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Type", "DRNumber", "Part Number", "Part Description", "Prod. Code - Line", "Current Part Cost", "MEAssignee", "QEAssignee", "DEAssignee", "EEAssignee", "OEAssignee", "SQEAssignee", "MRBHold", "Scrap Estimated Value", "MESignature", "QESignature", "DESignature", "OESignature", "EESignature", "SQESignature", "Closed By Signature", "Closed Date", "Create Date", "mrblocation", "wherefound", "disposition", "quantityreject", "responsibility", "defectcode", "workcenter", "containmentchangepersondate", "Supplier Name"}),
#"Added Prod Code column" = Table.AddColumn(#"Removed Other Columns", "Product Code", each Text.Start([#"Prod. Code - Line"], 4 )),
#"Renamed Columns1" = Table.RenameColumns(#"Added Prod Code column",{{"wherefound", "Where Found"}, {"containmentchangepersondate", "Containment Date"}, {"disposition", "Disposition"}, {"quantityreject", "Quantity Rejected"}, {"DRNumber", "DR Number"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"DR Number", Int64.Type}, {"Part Number", type text}, {"Part Description", type text}, {"Prod. Code - Line", type text}, {"Current Part Cost", type number}, {"MEAssignee", type text}, {"QEAssignee", type text}, {"DEAssignee", type text}, {"EEAssignee", type any}, {"OEAssignee", type any}, {"SQEAssignee", type text}, {"MRBHold", type logical}, {"Scrap Estimated Value", type number}, {"MESignature", type text}, {"QESignature", type text}, {"DESignature", type text}, {"OESignature", type text}, {"EESignature", type text}, {"SQESignature", type text}, {"Closed By Signature", type text}, {"Closed Date", type date}, {"Create Date", type date}, {"Supplier Name", type text}, {"Product Code", Int64.Type}, {"mrblocation", type text}, {"Where Found", type text}, {"Disposition", type text}, {"Quantity Rejected", Int64.Type}, {"responsibility", type text}, {"defectcode", type text}, {"workcenter", type text}, {"Containment Date", type date}}),
#"Filtered Rows - Closed dates" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNYears([Closed Date], 2) or Date.IsInCurrentYear([Closed Date]) or [Closed Date] = null),
#"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows - Closed dates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"
You were removing your row that contained the actual column names before you tried to promote that row to use its contents as the headers. Therefore, the Type column actually did not exist after promotion to headers. Based on the data in your screen clip, your column would instead be named Discrepancy, as your first row of actual data would be promoted.
Upvotes: 0