PowerBI - Remove Initial Records with 0 values

Question

I have a table in PowerBI that looks like this:

Date           StoreID       Car Sales     <Row Num (for explanation only)>
1/1/2017       1             0               1
1/2/2017       1             0               2
1/3/2017       1             0               3
1/4/2017       1             20              4 
1/5/2017       1             13              5
1/6/2017       1             0               6
1/7/2017       1             31              7

1/4/2017       2             0               8
1/5/2017       2             0               9
1/6/2017       2             7              10
1/7/2017       2             0              11
1/8/2017       2             10             12

What I am trying to do is create a measure that will calculate Car Sales by day (so on a line chart with Date on the x-axis), but eliminate the rows/records with 0's until the first Date that has a Sales value. In other words, I want to eliminate rows 1, 2, and 3, but not eliminate row #6, because that is a legitimate day where there were no cars sold. I also want to do this for every StoreID, so I want to eliminate rows 8 and 9, but not 11. Is there any way to come up with a measure/column (or other means) that will accomplish this in PowerBI?

Obviously, I can't just create a column that says IF( Table1[Car Sales] = 0, BLANK(), [Car Sales]) because that would eliminate days with no sales that are after the first sale. — SUMguy

Carl Walsh Carl Walsh · Accepted Answer · 2017-05-19T17:30:23

You can group by StoreID, and then transform each column with: sort by [Date], then use Table.Skip to remove rows where [Car Sales] is 0. (Sorting by [Date] might not seem necessary, but group by can change ordering.) Then expand out the grouped tables.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"StoreID", Int64.Type}, {"Car Sales", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"StoreID"}, {{"Grouped", (grouped) => let
            #"Sorted Rows" = Table.Sort(grouped,{{"Date", Order.Ascending}}),
            SkipNoCarSales = Table.Skip(#"Sorted Rows", each [Car Sales] = 0)
        in
            SkipNoCarSales, type table}}),
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Grouped Rows", "Grouped", {"Car Sales", "Date"}, {"Car Sales", "Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Grouped",{"Car Sales", "StoreID", "Date"})
in
    #"Reordered Columns"

PowerBI - Remove Initial Records with 0 values

1 Answers