Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Fill data for Missing Dates and Hours

In the attached set of data:

FYI: All those data should be continuos one. For better visualiztion, I split.

 

For July 1, 2nd-5th hour data is missing
And for July 2nd, 6th-15th hour data is missing.
For July 3rd, 0th-23th hour there is no data.
And on 4th July, full hour data is available.

 

I need to fill 0 for the missing hours of that particular date. Is there any way to get this done in Powerbi?

 

 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You could use GENERATESERIES with LOOKUPVALUE function. Basically Generate (1-24) values for a given Data, Add a calculated column with an IF Statement that will Lookup that values from the Dates table that you have, and if the result is BLANK() put 0 in Then Condition of the IF Statement.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Possibly. ImkeF might have an M solution. Only thing I can think of in DAX would be to use a GENERATESERIES, for example and generate hours 0-23. Then you could use that table and your original table to create a new table that included the missing rows. Sample data that can be compied would be great.

     

    Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    • ImkeF's avatar
      ImkeF
      Community Champion
      7 years ago

      If you create a table with rows 1-24 and merge that on JoinKind.FullOuter with your original tables, you'll get one row for each hour. 

       

      Then create a new conditional column that returns 0 if your "old" column "Hour" is null and if not, return "Value" instead.