Keep the source in a range and not in a table

[Mederic]

Hello everyone,

I'm trying to import a query into Power query without transforming the source into a table.
To do this, I have in the attached file, 2 queries that work but that I find a little slow despite the low volume of data
Is there a much faster and more efficient query?

Thanks in advance

Best regards

[PhilipTreacy · Accepted solution]

@Mederic 

Why do you want to do this?  You should try working with the data structures that PBI uses, it'll make life easier.

In the query in the Excel file you supply, your source data should be in an Excel table, not just a plain range.  This way you won't end up with lots of empty rows.

If you don't want the source as a table, what do you want? 

Regards

Phil

[PhilipTreacy]

@v-cgao-msft 

Why have you marked 3 solutions to this which only reinforce the wrong way to do things?  The OP themself said that using a table as the data source is faster.  Surely we should be teaching people the best way to do things?

Regards

Phil

[v-heq-msft]

Hi @Mederic ,

According to your description, you want to reference a range on an excel sheet as a data source and you want to improve the efficiency of the execution on that data source. As you and PhilipTreacy  said, using a table as a source will avoid a lot of null values. I agree with what Omid_Motamedise  said about filtering for null values if you need to simplify the steps. Of course you can also use the List.NonNullCount function. NonNullCount function directly to check the number of non-null values in a row, which avoids checking each column for nulls one by one, thus improving performance.

let
    Source = Excel.CurrentWorkbook(){[Name="A_C"]}[Content],
    Result = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Type = Table.TransformColumnTypes(Result,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}}),
    FilteredRows = Table.SelectRows(Type, each List.NonNullCount(Record.FieldValues(_)) > 0)
in
    FilteredRows

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Omid_Motamedise]

You have named the whole range of column A to column C by using the following code

= Excel.CurrentWorkbook(){[Name="A_C"]}[Content]

it  would be better to change the name of area include your data. If you insist to named this area and use the whole columns, in the first step, filter the nun cells

[Omid_Motamedise]

rewriting your query as bellow can be increase the speed also

let
Source = A_C,
#"Filtered Rows" = Table.SelectRows(Source, each ([a] <> null) or ([b] <> null) or ([c] <> null))
in
#"Filtered Rows"

[Mederic · Accepted solution]

Hello @Omid_Motamedise , @v-heq-msft , @PhilipTreacy ,
Sorry I'm back so late, but I've had a busy day.
Thank you for your suggestions, which I've just tried out.
For this small file, the execution time is more than 5-7 seconds for all tests.
So you're right, data in a table is more recommended.

Thanks again

Best regards

[PhilipTreacy]

@Mederic 

Not sure why you'd want to do this really or how it would be useful.  Just makes things harder when you're not using the data structures the program uses and relies on for its functions and other functionality to work.

Phil

[Mederic]

It was just out of curiosity, but it's true, it's not really recommended this way.

It's not very important for me to know how to do it. Have a nice evening

Best regards

[PhilipTreacy · Accepted solution]

@Mederic 

Why do you want to do this?  You should try working with the data structures that PBI uses, it'll make life easier.

In the query in the Excel file you supply, your source data should be in an Excel table, not just a plain range.  This way you won't end up with lots of empty rows.

If you don't want the source as a table, what do you want? 

Regards

Phil

[Mederic]

Hello @PhilipTreacy ,

Thank you for your reply,
I personally only use tables. I know that there are only advantages.
In this case, it's a simple exercise I found on the Web,
I wanted to see if it was possible to make it quicker and shorter.
I'm thinking that it might be useful one day.

Best regards