Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello, I'm new to power BI and now I'm facing a problem with missing data.
I've got a table recorded the growth rate of different kinds of virus as follow:
What I want to do is to create a new table and adding rows to the time point with missing data, and assuming that the growth rate remain the same as the previous reading until the next reading occurred. Like the following table:
I've created a new table with Unique Time point in each trial,
I want to know if it's possible to add missing data at corresponding time point in Power BI.
Any hints or advises would be much appreciated!
Solved! Go to Solution.
Hi @hokitkan,
You can create a table below:
Table 2 = UNION(ADDCOLUMNS(EXCEPT(CROSSJOIN(SUMMARIZE(Table1,[Colum1],[Colum2]),VALUES('Table'[Column4])),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4])),"Column3",0),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4],"Column3",[Column3]))
Then create a calculated column below:
Amount = if([Column3]<>0,[Column3], MAXX(FILTER(ALL('Table 2'),[Colum1]=EARLIER([Colum1])&&[Colum2]=EARLIER('Table 2'[Colum2])&&[Column4]<EARLIER([Column4])&&[Column3]<>0),[Column3]))+0
Best Regards,
Qiuyun Yu
Hi @hokitkan,
You can create a table below:
Table 2 = UNION(ADDCOLUMNS(EXCEPT(CROSSJOIN(SUMMARIZE(Table1,[Colum1],[Colum2]),VALUES('Table'[Column4])),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4])),"Column3",0),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4],"Column3",[Column3]))
Then create a calculated column below:
Amount = if([Column3]<>0,[Column3], MAXX(FILTER(ALL('Table 2'),[Colum1]=EARLIER([Colum1])&&[Colum2]=EARLIER('Table 2'[Colum2])&&[Column4]<EARLIER([Column4])&&[Column3]<>0),[Column3]))+0
Best Regards,
Qiuyun Yu
It works perfectly! Thanks for your efforts!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
72 | |
37 | |
31 | |
26 |
User | Count |
---|---|
96 | |
84 | |
43 | |
40 | |
35 |