Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Wouldn't it be more easy to create 3 request, whith
"Keep Errors"
Then combine together? and organize as deisred?
I do think the solution to the problem is probably a much simpler one. However, while I might go with a simpler solution, I am also from a technical point of view interested in why this code isn't working to improve my M skills
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.