Remove NaN with Direct Query

[Anonymous]

Is there a way to remove the rows where only NaN is stated in Direct Query?

[Anonymous · Accepted solution]

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!

[Syndicate_Admin]

https://community.dynamics.com/forums/thread/details/?threadid=928228ef-28e1-ee11-904d-00224827e220

[PijushRoy]

Hi @Anonymous 

Please go to the Power Query editor filter out NaN and load the data into the Power BI desktop.

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Anonymous]

That step ist not possible for the direct query, i would hace to change it to an import and i need the direct query

[PijushRoy]

Hi @Anonymous 

Did you try to filter with Direct Query and load the data?

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Anonymous]

Yes, i need to change the query type so that does not work