Test if table Empty

Power Query
1

I want to test IF the Excel table the Query has as its source is empty (no Rows). THEN message the user ELSE i want to perform the transformations.

I have the code below - This gives an error
Expression.Error: The import Changed Type matches no exports. Did you miss a module reference?

How do I fix the error

Alos will the code do the If Then Else that I want?

Thank You

Allister

  let
    Source = Excel.CurrentWorkbook(){[Name="t_AccredoTrialBalance"]}[Content],
    #"Check If table Empty" = if Table.Profile("t_AccredoTrialBalance")[Count] = Table.Profile("t_AccredoTrialBalance")[NullCount] then "All values are null" else
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",5),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Cleaned Text" = Table.TransformColumns(#"Transposed Table",{{"Column1", Text.Clean, type text}}),
    #"Transposed Table1" = Table.Transpose(#"Cleaned Text"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),

  
in
    #"Promoted Headers"
2

Hi @AllisterB,

As far as I know you can’t message the user based on whether a PQ query produced an empty table, “signals” can only be generated from threshold triggers in the PBI Service…

Note that another downside to producing an empty table could result in a refresh error IF there is a relationship between that table and another table in your Model.

BUT thinking creatively you could examine if it is possible…

  1. Test for an empty table = Table.IsEmpty
    https://docs.microsoft.com/en-us/powerquery-m/table-isempty

  2. See if a try-otherwise clause needs to be added to generate a result for (1)

  3. Optionally if (1) returns TRUE. Generate your own “my table is actually empty” table with the same structure, outputting a single row that includes a Key if there is a relationship so that doesn’t lead to a refresh error and a designated value in one of the other columns, syntax example:

    Table.FromRecords({
    [CustomerID = 1, Name = “Bob”, Phone = “123-4567”]
    })

  4. Create a measure to COUNTROWS matching the designated value in the column of that query

  5. Create a Card visual with that measure, Pin it to a dashboard in the Service and create the treshold trigger

.
Would like to know your findings :wink:
I hope this is helpful.

3

Thank You Melissa for your helpful reply.

However I am working to a deadline and I am still a novice and do not know the syntax well.

I will need to find another way.

Thanks again

Allister

4

Hi @AllisterB, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!