The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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?
Solved! Go to 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.
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.
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.
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.