Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rssilvaba
Resolver II
Resolver II

Empty sharepoint list does not load the columns.

Hi All,

 

I just realized that one of the report we designed has a sharepoint list data source that can be emptied. Now the dataset can't be refreshed and the measures from that respective table dont work.

Anyone knows any way to force PowerBI query to load the columns instead of checking if the table is empty and manually filling the columns?

I am trying to avoid the approach described here: https://community.powerbi.com/t5/Desktop/Error-when-SharePoint-list-is-empty/m-p/564396#M266324

5 REPLIES 5
Izs
Frequent Visitor

Hi,  I solve my problem using the above method by checking if the table is empty and manually filling the columns but only filling in the value for the relationship column and others blank (). That column is also the only selected column that I will use in my report and make sure the column name is the same as the real column to avoid any errors.

 

Here is my sample M code in Power Query,

 

 

let
Source = SharePoint.Tables("https://e.sharepoint.com/sites/a/ICT", [Implementation=null, ApiVersion=15]),

//Define the list ID
#"SPSource" = Source{[Id="SPSource"]}[Items],

//Check if Source is an empty table
//If yes, returns a table with a single row/column "Id=null"
//If not, does the rest of the code
CheckEmpty = if Table.IsEmpty(#"SPSource")
then
#table(
//type table [Id = number],
{
"clm_Title", // First Column Field Name
"clm_StatusOrder" ,
"WebUrl",
"clm_RequestDate"

},
{
{
"", // First Column Field Value
"",
"https://e.sharepoint.com/sites/a/ICT", // For relationship
""

}
}
)
else (
let

#"Added Custom" = Table.AddColumn(#"53507bbf-1338-4978-80aa-193a7408b62f", "WebUrl", each "https://e.sharepoint.com/sites/a/ICT"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"StatusOrder", Int64.Type}, {"ChangeImpactOrder", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Id", "clm_Id"}, {"Title", "clm_Title"}, {"Priority", "clm_Priority"}, {"Status", "clm_Status"}, {"StatusOrder", "clm_StatusOrder"}, {"ChangeImpactOrder", "clm_ChangeImpactOrder"}}),
#"Expanded Author" = Table.ExpandRecordColumn(#"Renamed Columns", "Author", {"FirstName"}, {"Author.FirstName"}),
#"Expanded ChangeInitiator" = Table.ExpandRecordColumn(#"Expanded Author", "ChangeInitiator", {"FirstName"}, {"ChangeInitiator.FirstName"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded ChangeInitiator",{{"Author.FirstName", "clm_Author"}, {"ChangeInitiator.FirstName", "clm_Initiator"}, {"RequestDate", "clm_RequestDate"}})
in
#"Renamed Columns1"
)
in
CheckEmpty

 

Hope it can help others.

Izs
Frequent Visitor

I had the same problem, yes how can we get the header only without the data? The column can be empty. The issue is for the relationship and calculated column. How can we solve this?
Anyone? Thanks in advance

VivekM
New Member

I know it's been a while. Just wanted to know if you got around this issue? I am facing the same issue.

 

Any help is appreciated. Thanks

v-eachen-msft
Community Support
Community Support

Hi @rssilvaba ,

 

You could try to remove the "Changed Type" step in the query editor.

However, why do you want to avoid the approach above?

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-eachen-msft ,

Removing "Changed Type" step does nothing to it, still does not bring the columns names. I think it is just an issue and hopefully ms will fix it. Why would I query a list if I can't retrieve it's columns? What about all the relationships and calculations that are already in place?

Anyways, I wanted to avoid that approach because you have to manually define each column for each table ahead of time, and most of the people who might need to change the report afterwards most likely will not understand there steps. For one table it the approach is fine but for a report that retrieves 6 lists not so much.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.