Create a empty Table if Table has no values

[Anonymous]

Hi,

I need to fill a empty Table with (empty) values, because my Data Load is based on the month and so it can be empty(with no Columns), but then PowerBI reports an error if I refresh the data.

Based on this solution (https://community.powerbi.com/t5/Desktop/Ignore-empty-table-on-load/td-p/254776) I created this query:

let
Source = #table(1,{OData.Feed("link"])}),

isEmpty = Table.IsEmpty(Source),


CheckEmpty = if isEmpty=true
then
#table(type table[Column1 = [ColumnNames... ],{})
else
Source,

#"Expanded Column1" = Table.ExpandRecordColumn(CheckEmpty, "Column1",{Columnnames..})
in
#"Expanded Column1"

But if the table isnt empty, I am getting a table full of errors. The Error Message is:

Expression.Error: We cannot convert a value of type Table to type List.

 

I am really confused, because I have no a idea where the error occurs.

Thank you guys.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think this issue should more related to your formula:

let
    Source = #table(1,{OData.Feed("link")}),
    #"Expanded Column1" = if Table.IsEmpty(Source)=true then #table(type table[#"Column1" ,type any],{}) else Table.ExpandRecordColumn(Source, "Column1",{"Columnnames"})
in
    #"Expanded Column1"

My test sample:

let
    Source = try OData.Feed("1https://services.odata.org/V3/Northwind/Northwind.svc") otherwise #table(type table[#"ColumnName"=text],{}),
    #"Changed Type" = if Table.IsEmpty(Source)= false then Table.TransformColumnTypes(Source, {{"Name",type any},{"Data",type any},{"Signature",type any}}) else Source
in
    #"Changed Type"

Regards,

Xiaoxin Sheng