Forum Discussion
Helper II am getting an empty table from Source after which when I try to expand a column am getting error
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 ?
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
5 Replies
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
NithyaKBHI Anonymous
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
Community Champion
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
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 columnsSource = 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.
