A challenging data manipulation problem: counts by dates to single rows

Hi,

According to your description, I create a table to test:

Please take following steps:

1) Unpivot columns:

2)Make table group by equipment:

3)Add a custom column to show each equipment’s detail data:

= Table.AddColumn(#"Grouped Rows", "CleanData", each Table.RenameColumns(
  Table.TransformColumnTypes(
    Table.SelectColumns(
      [Data],
      {"Attribute","Value"}
    ),
    {{"Attribute", type date},{"Value" , type number}}
  ),
  {{"Attribute","Date"},{"Value","Count"}}
))

4)Remove grouped column and add a custom column show each record of each equipment:

= Table.AddColumn(#"Removed Columns", "ConvertData", each let 
totalRow = List.Sum([CleanData][Count]),
listTable =  Table.FromList({0..totalRow}, Splitter.SplitByNothing(), {"Index"}, null, ExtraValues.Error),
dataTable = [CleanData],
t1 = Table.AddColumn(listTable,"subTable",each let i = [Index] in Table.SelectRows(Table.AddColumn(dataTable, "NewCount",each[Count] - i),each [NewCount]>0)),
t2 = Table.SelectRows(t1, each Table.RowCount([subTable])>0)
,
t3 = Table.AddColumn(t2, "Start Date",each List.Min([subTable][Date])),
t4 = Table.AddColumn(t3,"End Date", each List.Max([subTable][Date]))
in t4)

5)Expand this custom column and remove other unrelated column, the result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi