Forum Discussion

pfbrp's avatar
pfbrp
Frequent Visitor
1 year ago
Solved

Table.SelectRows on Sql.Database

The Soruce is an Sql.Database() returning a table of tables accessible through the [ Data ] column.

Some of those tables return data, but some are either empty or denying access.

I want to have a preview of the rows of the Soruce only where tables return data.

In order to achieve it, I do a simple selection:

try [ Data ]{ 0 } returns Expression.Error for empty returns and Datasource.Error for access denials.

Entire evaluation look simple, yet the PQ gets stuck loading everlasting thousands of rows.

Any ideas?

Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    Hi pfbrp, check this:

     

    Before

     

    After

    let
    Source = #table({"Data"}, {{null}, {#table(null, {})}, {error "Just test ERROR"}, {#table(null, {{1,2,3}})}, {"abc"}, {100} } ),
    FlteredTables = Table.SelectRows(Source, each (try [Data] otherwise false) is table)
in
    FlteredTables

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    Hi pfbrp, check this:

     

    Before

     

    After

    let
    Source = #table({"Data"}, {{null}, {#table(null, {})}, {error "Just test ERROR"}, {#table(null, {{1,2,3}})}, {"abc"}, {100} } ),
    FlteredTables = Table.SelectRows(Source, each (try [Data] otherwise false) is table)
in
    FlteredTables
  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi pfbrp  you could try simplifying the query and try this please as your query is evaluating the whole table on try....otherwise 
    Table.SelectRows(
    Source,
    each
    try Value.Is([Data]{0}, type table) otherwise false
    )
    If this post helped please do give a kudos and accept this as a solution 
    Thanks In Advance

    • pfbrp's avatar
      pfbrp
      Frequent Visitor
      1 year ago

      With small correction (testing for record instead of table):

      I state that the effect is the same.

      Did you mean that a whole table is evaluated at each Table.SelectRows() iteration? Why would it be if I'm checking on the first row only?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi pfbrp,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    It looks like you are trying to filter out tables that are empty or inaccessible. As dufoq3 and Akash_Varuna both gave responses of their own for your query, can you please confirm if you were able to solve your issue with the help of their responses. If yes, then please mark the helpful response as solution.

     

    I would also take a moment to thank dufoq3 and Akash_Varuna, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.