Power Query - Prevent complete dataset to be refreshed when there's something wrong with the source

[steverely]

Suppose you use a source table in your report that is loaded thru a ETL backend system and this table is the primary table in your dataset. Your ETL system loads the table every day, but on friday the 13th 🙂 there is a failure and the table gets truncated for some reason.

Offcourse you don't want your Power BI Dataset to load that table or all tables because it's empty and can generate other report errors. In a proactive situation you don't want to refresh that Power BI report so the end users doesn't see a empty report.

Is there a way in Power BI power query itself to check if the complete dataset may be refreshed.

For example you have a refresh status table in you backend system that Power BI Power query has to check if the dataset may be refreshed or not.

I know you can check this with power automate or something else, but my question is Power Query itself

[edhans]

Sure. You would need to do a row count on the source table. Then if it has 0 records, return an error. That will halt the query refresh.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLGMwyBrJMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    IsValidData =
        if Table.RowCount(#"Changed Type") = 3
            then
                error [ 
                    Reason = "SourceTableEmpty", 
                    Message = "Source table has no records", 
                    Detail = "Database.TableName" 
                ]
        else #"Changed Type"
in
    IsValidData

In this example, I tested for 3 records so it would error out. But use 0 in your actual use case. It will return that error to the service.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[steverely]

Thanks for the reply. As soon as possible I'll test it.

[edhans]

I know it works, because after I came up with it, I implemented it on a dataset I was having a similar issue with, and your question sparked the answer I needed. 😂

Let me know if you have any questions!