Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
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.
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
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
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?
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |