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

[Anonymous]

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. 

[v-gizhi-msft · Accepted solution]

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

[Anonymous]

Legendary! Thanks Giotto, I got it working. 

There's no way I would have figured that out on my own. 

[amitchandak]

Check if can solved like Current employee problem.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi Amitchandak, 

Thanks for your reply, unfortunately this isn't the solution I'm looking for. My number one is the Power Query side where I transform the top table into the bottom table.