When your query returns an error, sometimes it could make sense to return an empty table instead of a simple error message. And ideally, this table has the same columns and types than the table that would have been returned without the error. Check out Chris Webbs article about it here: Chris Webb’s BI Blog: Handling Data Source Errors In Power Query Chris Webb’s BI Blog (crossjoin.co.uk)
Create empty table function
The function I’m sharing here creates a string with M-code that creates an empty table with the same column names and types than the original table automatically for you. All you have to do is reference the table without errors in the function parameter.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| fnFormatted = let | |
| func = (myTable as table) => | |
| let | |
| #"Table1 Schema" = Table.Schema(myTable), | |
| TypesList = Table.AddColumn( | |
| #"Table1 Schema", | |
| "TypeRecord", | |
| each [Name] & "=" & [TypeName] | |
| )[TypeRecord], | |
| TypeRecordString = "[" & Text.Combine(TypesList, ", ") & "]", | |
| TableString = "#table(type table " | |
| & TypeRecordString | |
| & ", {{" | |
| & Text.Combine(List.Repeat({"null"}, List.Count(TypesList)), ", ") | |
| & "}})" | |
| in | |
| TableString, | |
| documentation = [ | |
| Documentation.Name = " Text.CreateEmptyTableFromSchema ", | |
| Documentation.Description | |
| = " Creates the M-code for an empty table based on a table schema. ", | |
| Documentation.LongDescription | |
| = " Creates the M-code for an empty table based on a table schema (Table.Schema). ", | |
| Documentation.Category = " Text.Transformations ", | |
| Documentation.Source = " www.TheBIcountant.com https://wp.me/p6lgsG-2tJ . ", | |
| Documentation.Version = " 1.0 ", | |
| Documentation.Author = " Imke Feldmann ", | |
| Documentation.Examples = { | |
| [Description = " ", | |
| Code = | |
| " let | |
| myTable = #table( type table [myText = Text.Type, myNumber = Int64.Type, myDate = Date.Type], | |
| // myText| myNumber| myDate| | |
| {//——-|———|——————| | |
| { ""A"", 10, #date(2022, 01, 01) } } ), | |
| FunctionCall = fnText_CreateEmptyTableFromSchema( myTable ) | |
| in | |
| FunctionCall " | |
| , | |
| Result = " #table(type table [myText=Text.Type, myNumber=Int64.Type, myDate=Date.Type], {{null, null, null}}) " | |
| ]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) | |
| in | |
| fnFormatted |
Copy the code above and paste it into the advanced editor in Power Query.
It will return a function that asks for 1 parameter: The table that shall be replicated. It will then return the M-code that you can use in the “otherwise” branch of your error handling.
How does it work?
It creates the table string from schema, using the Table.Schema M-function (row 5). It then adds a column that reads out column names and types from the schema table to create the string for the type record (row 6-10). Then it concatenates everything to create the M-code for an empty table like so:
#table(type table [myText=Text.Type, myNumber=Int64.Type, myDate=Date.Type], {{null, null, null}})
Enjoy & stay queryious đ
Pingback: Returning an Empty Table in Power Query – Curated SQL