Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks for the reply. As soon as possible I'll test it.
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!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting