Hi Community.
Below code produces the error "An error occurred in the " query. Expression.Error: The name 'Availability' wasn't recognized. Make sure it's spelled correctly".
I'm fairly certain this isn't a spelling mistake, but what do I know. I have 3 tables (M_Extract_Availability, M_Extract_Request, M_Extract_TimeReg). The idea is that in these tables I filter out very early various errors (such as # instead of a foreign key which is stored as an int). However, I want to produce a table (this query) that outputs the following:
1) Name of Table where the error is happening
2) Which error
I'd prefer if this could be contained to a single query which is why I'm using multiple let/in statements. Code starts by defining the rows and columns of the tables I want to return, and then defines each table with a let / in statement where I don't filter out the #. I then check whether the row count of the original table (with no #) is less than the one without the filter. If so I want to return whichever table matches the error.
I appreciate the help 🙂
let
Source = {},
#"Table1" = #table({"Table","Cleaning needed"},{"Request","# Removal"}),
#"Table2" = #table({"Table","Cleaning needed"},{"Availability","# Removal"}),
#"Table3" = #table({"Table","Cleaning needed"},{"Time Reg","# Removal"}),
#"Table4" = #table({"Table","Cleaning needed"},{}),
Availability = let
Source = Excel.Workbook(Web.Contents(""&#"SharePointFileURL"&""), null, true),
Availability_Sheet = Availability{[Item="Availability",Kind="Sheet"]}[Data],
#"Removed Top Rows A" = Table.Skip(Availability_Sheet,1),
#"Promoted Headers A" = Table.PromoteHeaders(#"Removed Top Rows A", [PromoteAllScalars=true])
in
#"Promoted Headers A",
Request = let
Source = Excel.Workbook(Web.Contents(""&#"SharePointFileURL"&""), null, true),
Request_Sheet = Source{[Item="Request",Kind="Sheet"]}[Data],
#"Removed Top Rows R" = Table.Skip(Request_Sheet,1),
#"Promoted Headers R" = Table.PromoteHeaders(#"Removed Top Rows R", [PromoteAllScalars=true])
in #"Promoted Headers R",
TimeReg = let
Source = Excel.Workbook(Web.Contents(""&#"SharePointFileURL"&""), null, true),
TimeReg_Sheet = Source{[Item="Time reg",Kind="Sheet"]}[Data],
#"Removed Top Rows TR" = Table.Skip(TimeReg_Sheet,1),
#"Promoted Headers TR" = Table.PromoteHeaders(#"Removed Top Rows TR", [PromoteAllScalars=true])
in #"Promoted Headers TR"
in
if
//All 3
Table.RowCount(M_Extract_Request)<Table.RowCount(Request) and
Table.RowCount(M_Extract_Availability)<Table.RowCount(Availability) and
Table.RowCount(M_Extract_TimeReg)<Table.RowCount(TimeReg) then
Table.Combine({#"Table1", #"Table2",#"Table3"}) else if
//Request & Time Reg
Table.RowCount(M_Extract_Request)<Table.RowCount(Request) and
Table.RowCount(M_Extract_Availability)<Table.RowCount(Availability) and
Table.RowCount(M_Extract_TimeReg)<Table.RowCount(TimeReg) then
Table.Combine({#"Table1", #"Table3"}) else if
//Request & Avalability
Table.RowCount(M_Extract_Request)<Table.RowCount(Request) and
Table.RowCount(M_Extract_Availability)<Table.RowCount(Availability) then
Table.Combine({#"Table1", #"Table2"}) else if
//Availability & Time Reg
Table.RowCount(M_Extract_Availability)<Table.RowCount(Availability) and
Table.RowCount(M_Extract_TimeReg)<Table.RowCount(TimeReg) then
Table.Combine({#"Table2",#"Table3"}) else if
//Just request = table 1
Table.RowCount(M_Extract_Request)<Table.RowCount(Request) then #"Table1" else if
//Availability
Table.RowCount(M_Extract_Availability)<Table.RowCount(Availability) then #"Table2" else if
//Time Reg
Table.RowCount(M_Extract_TimeReg)<Table.RowCount(TimeReg) then #"Table3" else #"Table4"
