Introduction
In Power Query, handling empty queries is a common requirement when dealing with complex data transformations. Empty queries can occur due to various reasons, such as incorrect data sources, query errors, or filters that return no results. In this article, we will explore how to control output power query by handling empty queries effectively.
Understanding Empty Queries
An empty query in Power Query is a query that does not return any records. It may occur due to various reasons, such as:
- Incorrect data source
- Query errors
- Filters that return no results
Empty queries can cause issues when you try to apply further transformations or load the data into a model. Therefore, it is essential to handle empty queries appropriately to prevent errors and maintain data integrity.
Handling Empty Queries
Power Query provides several ways to handle empty queries:
Using Error Handling
One way to handle empty queries is by using error handling. You can wrap your query steps in a Try/Catch block to handle any errors that may occur, including those caused by empty queries:
let Source = try (
Excel.CurrentWorkbook(){[Name="Table1"]}[Data]
) otherwise null,
KeptRangeRows = if isError(Source) then null else Table.SelectRows(Source, each [Name] in List.Distinct(Table.ColumnNames(Source)) && [Name] <> "KeptRangeRows")
in KeptRangeRows
Using Null Coalescing Operator
Another way to handle empty queries is by using the null coalescing operator. You can use the double ampersand (&&) operator to provide a default value when the query returns an empty result:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Data] && Table.SelectRows(Source, each [Name] in List.Distinct(Table.ColumnNames(Source)) && [Name] <> "KeptRangeRows"),
KeptRangeRows = Table.SelectRows(Source, _)
in KeptRangeRows
Using If Statement
You can also use an if statement to check if the query returns an empty result and provide a default value:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Data],
KeptRangeRows = if isEmpty(Source) then null else Table.SelectRows(Source, each [Name] in List.Distinct(Table.ColumnNames(Source)) && [Name] <> "KeptRangeRows")
in KeptRangeRows
Summary
In this article, we explored how to control output power query by handling empty queries effectively. We covered three ways to handle empty queries: using error handling, null coalescing operator, and if statement. By using these techniques, you can prevent errors, maintain data integrity, and ensure that your queries return the expected results.