Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Hi Power BI Community, 

 

I have a challenging data manipulation problem for you...

 

In our org we manage construction projects and allocate assets, people, equipment, etc. to projects. Many of the project forecasts come like this, with each number representing the equipment count for that week: 

 

 

The challenge is that in Power BI, we want it to look like a Gantt Chart. For that I need the data to be in the format below, with each of the counts above being expanded into a row, translating the dates above to a start and end date (note I haven't added 6 days to the end date below to represent the end date of the week, that's an easy step I can do afterwards). My biggest challenge is translating the above table to the below: 

 

 

I look forward to hearing your suggestions. 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

4 Replies