Blank values coming is as tables in a text column

[elaine1217]

I have data coming in from a Sharepoint List. I have a function that gets all the past versions of each list item. I have a text column, but if there are any blank values, they come in as a blank table instead of blank field. This gives me an error anytime I try to filter. I can't get rid of the rows. How can I replace these with a blank/null value?

[Jaywant]

To replace the blank tables with a blank or null value in Power BI, you can use the following steps:

1. Open the Power Query Editor in Power BI Desktop.

2. Locate the query that retrieves data from your SharePoint List and click on it.

3. In the Power Query Editor, find the step where the blank tables are appearing. It might be a step where you are expanding or transforming data.

4. Add a new step by clicking on the "Add Column" tab in the Power Query Editor and selecting "Custom Column" from the drop-down menu.

5. In the "Custom Column" dialog box, provide a name for the new column, for example, "FilteredColumn".

6. In the "Custom column formula" field, enter the following formula to check if the value is a table or blank and replace it with a null value:

   if Value.Is(Table.Column([YourColumnName], 0), type table) then null else [YourColumnName]

   Replace [YourColumnName] with the actual name of your text column.

7. Click OK to create the new column.

8. Remove the original column containing the blank tables by selecting the column and clicking on the "Remove Columns" button in the Home tab.

9. Click on the "Close & Apply" button in the Home tab to apply the changes and load the data into Power BI.

By following these steps, the blank tables in your text column will be replaced with null values, allowing you to filter the data without encountering any errors.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elaine1217]

When trying this I received the following error message - 

Expression.Error: We cannot convert the value "This is a text value" to type Table.
Details:
Value=(this is a text value)
Type=[Type]

[Anonymous]

Hi @elaine1217 ,

Which error message did you get? And could you please provide the codes of that function? Please review the following links to make error handling and check if it can avoid the error.

M Language Error Handling - PowerQuery M | Microsoft Learn

try error "A" otherwise 1 

How to check for nulls in Power Query (IsBlank and IsNull) - Smantin Data

Best Regards