Date ranges and numbers.from makes my query extremely slow

[EBoklund]

I have a dataset with close to 400K rows. This is a historical list (for historical statistics) of members of the labor union, I work for, and each member has range dates for their membership in a given membership category (so if a member changes category, he or she gets another row with new date ranges and a new category). One column contains the start date of the range, and another the end date.

Now, I tried to create a custom column using the code below, to get a row for each day the member had been a member of the union:

={Number.From([Start Date])..Number.From([End Date])}

It works fine. But... when I choose "Close & apply", the query gets extremely slow, probably because the dataset exploded to an insane size, and only loads bout one row each second. Before I applied it, it loaded the 400K rows in mere seconds.

I tried to filter the column in the query (as I only need the 1st day of every month they were members), but that doesn't seem to help.

So, what I am asking is if there is any way to reduce the data load to a size that is manageable and doesn't slow my data model down to snail speed? 

[v-stephen-msft]

Hi @EBoklund ,

Try to refer to the official document to optimize your data:

Data reduction techniques for Import modeling - Power BI | Microsoft Docs

Optimization guide for Power BI - Power BI | Microsoft Docs

Power BI Best Practices | MAQ Software Insights

Best Regards,

Stephen Tao

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

[EBoklund · Accepted solution]

Thanks. However, I found the sinner. It turned out, that open memberships had an end date in the year 9999. The number.from command inflated those days into an insane amount of rows. After replacing the date value with TODAY(), everything began to run smoothly.