If table is empty, insert null value, else carry on.

[Anonymous]

Hello community,

I am struggling with a bit of M code.

I am trying to search for a value which in some cases is not found, resulting in an empty table. For reference, the table has only 1 column, and is expected to have 1 value returned, which I then need to reference in another query. I would like to put in an if statement, checking if the table is empty, and if so, add a single null value to make the table not empty (so it doesn't throw up an error when referenced, it just returns a blank). I am struggling to get the code right and any help here would be highly appreciated. 

These are my last few lines of code.

    #"Removed Other Columns3" = Table.SelectColumns(#"Renamed Columns2",{"BatchNo"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Other Columns3",{{"BatchNo", type text}}),
    #"AddNull" = if Table.IsEmpty(BatchNumber2) then Table.InsertRows(#"Changed Type3",0, { [BatchNo = "null"] }) else #"Changed Type3",
    #"Search Batch Number" = #"AddNull"{0}[BatchNo]
in
    #"Search Batch Number"

The Table.InsertRows section seems to work (I tried it without the if statement at first), but if the table is populated, I don't want to add a null value. I need to add something to the else statement but if false I just want the query to continue to the next line (Search Batch Number). I read somewhere to just use the name of the previous step to carry on but this hasn't worked as I now get a cyclic reference error.

Thanks in advance for your help.

[edhans]

Just create a table with 1 record, vs trying to add records to the empty table. See this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
    Custom1 = 
        if Table.IsEmpty(Source) then 
            #table(
                {"Test"},
                    {
                        {1}
                    }
            ) else Source
in
    Custom1

The Source line has nothing it, just a Test column, no records, so Table.IsEmpty returns true.

So it generates this table:

If it isn't empty, then it returns the Source table.

You can change the {1} to {null} or {"Some text"} or whatever.

EDIT: I did look at your code more closely. It is hard to know why it is failing because you didn't show us what Changed Type3 was, and what it is dependent on. This code will add a null to the existing table though if it is empty:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
    #"AddNull" = if Table.IsEmpty(Source) then Table.InsertRows(Source,0, { [Test = "null"] }) else Source
in
    AddNull

I think your circular reference issue though is you are referring to other steps, and I cannot see what those steps are so they may have interdependencies that are causing it.

[Anonymous · Accepted solution]

I figured it out.... the table name is the name of the previous step. I was calling it by a different name. It's always a small problem isn't it.... anyway thanks for your help!

The end of the code now reads:

#"Removed Other Columns3" = Table.SelectColumns(#"Renamed Columns2",{"BatchNo"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Other Columns3",{{"BatchNo", type text}}),
    #"AddNull" = if Table.IsEmpty(#"Changed Type3") then Table.InsertRows(#"Changed Type3",0, { [BatchNo = "null"] }) else #"Changed Type3",
    #"Search Batch Number" = #"AddNull"{0}[BatchNo]
in
    #"Search Batch Number"

All I had to do was change Table.InsertRows(BatchNumber2, 0, { [BatchNo = "null"] }) to Table.InsertRows(#"Changed Type3", 0, { [BatchNo = "null"] }).

[edhans]

Glad you got it solved @Anonymous