Forum Discussion
Post PatronKeep the source in a range and not in a table
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
Hello Omid_Motamedise , Anonymous , 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
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