Rows not columns

[rudiklein]

Hi,

I wonder if some of you can help me with the following challenge.

Part of my table looks like the one below (current). I have a start date of a timecard, which is allways Monday. So for Mondays I have an actual date, but not for the the other days of the week. This means it is very hard to make a report over a certain period, lets say a month. I would like the table to look like the second table below (need).

Untill now I have solved it with some coding in Access and some stacked queries and build the visuals in QlikView, but I want to get rid of Access in this particular case and move to Power BI. 

Transposing seems not to be the solution, because I need timecards-ID's to be duplicated, not transposed. Only the week days need to be transposed. I also need to generate the timestamps for the days of the week, not being Monday.

The most favourable way to do this would be at the source data, but that is not going to happen.

Next stop would be in the Power BI query, but for now, I don't have a clue how to attack this problem.

Any help, solutions or hints, not necessarily for a solution on query level, will bring you great karma and my greatest thanks 🙂

Bye,

Rudi

Current

timecard   start date     monday   tuesday   wednesday   thursday   friday   saturday   sunday

123456      1-10-2017        3              4                 1                 0             5            1             0

123457      1-10-2017        2              3                 2                 4             1            0             0

Need

timecard   actual date   day               hours

123456      1-10-2017      monday        3

123456      2-10-2017      tuesday        4

123456      3-10-2017      wednesday   1

123456      4-10-2017      thursday       0

123456      5-10-2017      friday            5

123456      6-10-2017      saturday       1

123456      7-10-2017      sunday          0

123457      1-10-2017      monday        2

123457      2-10-2017      tuesday         3

123457      3-10-2017      wednesday    3

123457      4-10-2017      thursday        4 

123457      5-10-2017      friday             1

123457      6-10-2017      saturday        0

123457      7-10-2017      sunday          0

.

.

.

[MarkS · Accepted solution]

Hi,

To do this in PowerQuery(Query Editor)

1. Select the Columns Monday through Sunday
2. On the Transform Tab select Unpivot Columns
3. Then on the Add Column Tab select add index (from zero)
4. Then select that index column and on the Transform Tab in Number Column-Standard select modulo and select 7
5. Then select add Column-Custom Column and the formula is Date.AddDays([Startdate],[Index])
6. Rearrange and rename columns and delete unnecessary columns as desired.

[rudiklein]

Hi MarkS,

Spot on! A really nice and clean solution to my problem. The one I thought of myself was much more complicated (7 queries, a query for each day).

It worked perfectly.

Thanks for you great help.

Rudi

[parry2k]

Agreed  wih @MarkS, unpivot feature will do it. Let us know if it worked out or need further help to unpivot it?

[CahabaData]

besides Phil's approach - I was thinking there is an UnPivot feature in the query editor.  I myself haven't needed to use it - so am not 100% sure but the idea is in the back of my mind that there is...... hope I'm not sending you on a wild goose chase but it would only take a minute to review that....

[Phil_Seamark]

This must be close...  Create this as a new table

 Need =
 SELECTCOLUMNS (
     FILTER (
         CROSSJOIN ( 'Current', Dates ),
         'Dates'[Date] >= 'Current'[start date]
             && 'Dates'[Date]
             < 'Current'[start date] + 7
     ),
     "Time Card", 'Current'[timecard],
     "Acutal Date", 'Dates'[Date],
     "Day", FORMAT ( 'Dates'[Date], "DDD" ),
     "Hours", SWITCH (
         FORMAT ( 'Dates'[Date], "DDD" ),
         "Mon", 'Current'[Monday],
         "Tue", 'Current'[Tuesday],
         "Wed", 'Current'[Wednesday],
         "Thu", 'Current'[Thursday],
         "Fri", 'Current'[Friday],
         "Sat", 'Current'[Saturday],
         "Sun", 'Current'[Sunday],
         -1
     )
 ) 

[rudiklein]

Thanks Phil.

MarkS came up with an even better solution, but thanks for thinking with me.

Rudi