Remove NaN with Direct Query

Hi Anonymous ,

The method provided by PijushRoy is quite good. If it does not work, please try mine.

When connecting to the data source, expand the Advanced, you will see the following options as shown above.

Command timeout in minutes: This option provides the time it takes the query to execute in SQL Server. If your connection lasts longer than 10 minutes (the default timeout), it will terminate; else, you can enter another value in minutes to keep the connection open longer. This option is only available in Power Query Desktop.

SQL statement (optional, requires database😞 This is where you write or paste the query that will filter the database and return the necessary information. When its completes execution, it will automatically load to Power Query for transformation.

Include relationship columns: If checked, includes columns that might have relationships to other tables. If this box is cleared, you won’t see those columns. This is important if your query has SQL JOINS.

Navigate using full hierarchy: If checked, the Navigator displays the complete hierarchy of tables in the database you’re connecting to. If cleared, Navigator displays only the tables whose columns and rows contain data.

Enable SQL Server Failover support: If checked, when a node in the SQL Server failover group isn’t available, Power Query moves from that node to another when failover occurs. If cleared, no failover will occur.

Please try:

Enter a SQL statement in SQL Statement and import only the data whose 'Energy yield [GWH]' column is not null.

The specific SQL statements may vary depending on the data source.

For example, the SQL statement of the SQL Server data source is:

SELECT *
FROM Test1
WHERE Energy yield [GWH]IS NOT NULL
   AND Energy yield [GWH]<> '';

Among them, Test1 is the table name and Energy yield [GWH] is the column name.

Once you are done, click Ok. It will automatically load to Power Query for transformation.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!