Dont display table if empty

[jijoythomas29]

Hi,

I have two tables (A and B) that merge as a single table(C), 

If table B  is empty i dont want the table(C) to be displayed on the sheet.

I have tried ISempty function for some reason i feel like im not doing it right.

Any help would be appreciated!

[miguel]

If this for Power Query in Excel, perhaps you could create your own VBA code that first runs a query to test if Table B is empty and if its not then it then evaluates the full query. Otherwise the VBA code will clean the sheet.

It's mainly about leveraging VBA for your solution.

[jijoythomas29]

is there a way to clear the table if there is no more than 2 rows in the table using VBA? 

I know how to clear the worksheet but not sure how to clear the specific table based on the condition.

[jbwtp]

Hi @jijoythomas29,

the code itselfis fairly simple:

Private Sub Workbook_ModelChange(ByVal Changes As ModelChanges)
    
    tblExists = False
    
    If Changes.TablesModified.Count > 0 Then Exit Sub
    
    On Error Resume Next
        tblExists = ActiveSheet.ListObjects("Query1").Name = "Query1"
    On Error GoTo 0
        
    
    If tblExists Then
        ActiveSheet.ListObjects("Query1").Refresh
    Else
        With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
            Connections("Query - Query1"), Destination:=Range("$A$2")).TableObject
            .RowNumbers = False
            .PreserveFormatting = True
            .RefreshStyle = 1
            .AdjustColumnWidth = True
            .ListObject.DisplayName = "Query1"
            .Refresh
        End With
    End If
    
    If ActiveSheet.ListObjects("Query1").Range.Rows.Count < 3 Then ActiveSheet.ListObjects("Query1").Delete
    
End Sub

Above, we check if the table exists (cos we gong to delete the emplty this may not be the case) and delete it. Then we add it back and check how may rows it has, if only two then we delete it. Just change the code to your Workbook section of your VBA code. It will run on RefreshAll, but you will need to initially set the query to feed to your Data model in the Excel file (using Load To...).

Do you want to make the file macro-enabled only for this benefit?

Kind regards,

John

[jbwtp]

And if you have any other tables in the file, the code may nee dto be updated to let them refresh correctly. I did not tested that.

[miguel]

There's probably a way to check if the table X amount of rows and then you can delete the table in VBA.

I'd recommend posting questions around VBA for Excel on the forum below:

office-vba-dev - Microsoft Q&A

These sorts of automations are primarily driven outside of Power Query and only enabled or limtied by the product integration which in this case is Excel, so it is recommended that you post this question in a forum more focused towards Excel like the one I provided above.

Best!

[jbwtp]

Hi @jijoythomas29,

I don't think PQ can "delete" table from Excel if it is empty. On the Excel side, it is a "connection" (like a data pipe from PQ), PQ does not have control over it. Excel does not like if you don't return a table, the query in this case would exit with an error. The closest that I think you can potentially get to what you need is to load a one column, empty table like this:

let
    A = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    B = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Filtered Rows" = Table.SelectRows(B, each [Column1] = "A"),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Column1"}, #"Filtered Rows", {"Column1"}, "B", JoinKind.LeftOuter),
    C = if Table.IsEmpty(#"Filtered Rows") then #table({"Empty"},{}) else Table.ExpandTableColumn(#"Merged Queries", "B", {"Column1"}, {"B.Column1"})
in
    C

Cheers,

John

[jijoythomas29]

Thank you! 
Let me test this out!