Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

Reply
PowerBICouple
Regular Visitor

M-code producing unexpected error. Syntax question

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"

2 REPLIES 2
SebSchoon1
Post Patron
Post Patron

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.