Forum Discussion
Generate missing data at certain time points
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!
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
2 Replies
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 YuIt works perfectly! Thanks for your efforts!
