Forum Discussion
If table is empty, insert null value, else carry on.
- Anonymous5 years ago
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"] }).
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.
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"] }).
- edhans5 years agoCommunity Champion
Glad you got it solved Anonymous