CODEWITHSUNDEEP
excelpowerquery
Mike M
Mike M

Reputation: 488

Check if Power Query table is Empty then return something else

I have a Power Query table that is pulling in Data from an ODBC connection, filtering that data and then returning it into excel. I then have several columns added to the table in excel that are utilizing the Excel networkdays formula. My issue is that occasionally the Power Query table will be empty and when empty the NetWorkdays formulas disappear from the excel table.

My question is this. Is there a way to either check if Power Query is empty and return 0's or something else that will prevent the formula's in the excel cells from disappearing, or a way to prevent excel from removing the formula's if the table is empty?

Upvotes: 2

Views: 9007

Answers (2)

philu
philu

Reputation: 874

You can edit the query and use the "if" function, e.g.

let
    #"Some Result" = if not List.IsEmpty(#"Some List") then
            #"Some List"
        else
            {"define","some","default","list","here"}
in
    #"Some Result"

Upvotes: 1

Fercstar
Fercstar

Reputation: 57

I found a way to make it so that Power Query will return a blank row if there is no data in the query result, but it is a bit of a workaround.

  1. Create a table in the workbook that has the exact same columns as the table being returned by Power Query (don't include the column you added for your NETWORKDAYS formula). The table should only have one row which can be blank.
  2. Create a new query from the Table you just created and append your ODBC query to it.
  3. Add an index From 1 (this will allow us to identify which row in your query is from the table you created so that we can filter it out if your ODBC query returns rows)
  4. Create a duplicate of your ODBC query. This is so we can determine if the query will be returning rows.
  5. Open the duplicate up in the query editor. Add a custom column called "Group" use the formula ="Group" to add a column that has "Group" in every row.
  6. On the Transform Tab, do a Group By and Group by the Group column you created in step 3 with a new column named Count with the Count Rows operations. This will return a one row table with two columns (Group and Count).
  7. Add another custom column using this formula =if [Count]>0 then 1 else 0
  8. Now go back to the appended query you created in Steps 2-3 and merge it with the query you created in Steps 4-7. Merge it on the Index column (from your Steps 2-3 query) and the Custom column you added in Step 7 (to your Steps 4-7 query). Be sure to use Join Kind Left Anti (this will only return rows that don't have a match in the second query.

In the end, if there is no data returned from the ODBC query, it will load the row from the table you created in Step 1. If there is data in the ODBC query, the row from the Step 1 table will get filtered out by the Left Anti Join.

Upvotes: 1

Related Questions