I am getting an empty table from Source after which when I try to expand a column am getting error

[NithyaKB]

Hi

When I have atleast 1 record in an table the expand author  functionality is working fine. But If i get an empty table from source I am getting an error message "The Column Author of the table was not found".

In the above image the table is empty from Source , When I try for expand author column for each books.

Below is the code for reference

Source = lists/getbytitle( list name)/select id, books,author/Title&Expand = "Author",

"Added Custom" = Table.Add Column(Source , "Title" , each "Shakespeare")

"Expanded Author" = Table.ExpandRecordColumn(#"Added Custom" , "Author",{"Title"},{"Author"})

in

#"Expanded Author"

Is there anyway even if the table is empty it should expand author column along with all the columns specified in Source ?

[v-yiruan-msft · Accepted solution]

Hi @NithyaKB ,

This problem seems to be caused by the absence of source table data information, resulting in an error in expand columns due to the Author column not being found. Please update the applied codes in your Advanced Editor as below and check whether it can work:

let
  Source = lists/getbytitle( list name)/select id, books,author/Title&Expand = "Author",                                                                               
  CheckEmpty = 
    if Table.IsEmpty(Source) then
      #table(type table [Author = [Author = any]], {})
    else
      Source, 
  #"Added Custom" = Table.AddColumn(CheckEmpty, "Title", each "Shakespeare"), 
  #"Expanded Author" = Table.ExpandRecordColumn(#"Added Custom", "Author", {"Title"}, {"Author"})
in
  #"Expanded Author"

If the above one is not working for your scenario, please share some sample data in your source table when there is some data in it. Later we will provide you a suitable solution base on your shared information. Thank you.

Best Regards

[NithyaKB]

HI @v-yiruan-msft 
I need to give 3 columns in expand category . 
let
Source = lists/getbytitle( list name)/select id, books,author/Title&Expand = "Author","Books" , "Editors"
CheckEmpty =
if Table.IsEmpty(Source) then
#table(type table [Author = [Author = any]], {})
else
Source,
In this case sould you please tell me how to include 3 expanded columns(Author , Books,Editors) in check empty condition . In current condition it has only 1 expanded column that is Author.

[Greg_Deckler]

@NithyaKB Try:

Source = lists/getbytitle( list name)/select id, books,author/Title&Expand = "Author",
"Added Custom" = Table.Add Column(Source , "Title" , each "Shakespeare")
"Expanded Author" = if #"Added Custom" = null then #"Added Custom" else Table.ExpandRecordColumn(#"Added Custom" , "Author",{"Title"},{"Author"})
in
#"Expanded Author"

[NithyaKB]

@Greg_Deckler 

I tried the below query as suggested .But still facing same issue.

Please find below the screenshot of error message.

[NithyaKB]

@Greg_Deckler 

Can you suggest how to get the multiple columns

Source = lists/getbytitle( list name)/select id, books,author/Title&Expand = "Author",
"Added Custom" = Table.Add Column(Source , "Title" , each "Shakespeare")
"Expanded Author" = if #"Added Custom" = null then #"Added Custom" else Table.ExpandRecordColumn(#"Added Custom" , "Author",{"Title"},{"Author"})
in
#"Expanded Author"

(id , books ,authors) , If these columns are emoty then it should return empty tables.