Power Query - Add Columns Conditional

Sherri · Feb 26, 2017

I am a very new user of Power Query. I am trying to build either a Power Pivot report or a Power BI Dashboard for a customer that who not an Excel Nerd like me. I normally solve this problem with some fairly annoying Excel transformations that this user cannot be expected to do.

My task is to create a view of clients served by hour. (The information will be used to support strategic plans).

Client records include a "time in" and "time out" value for each day for each type of service provided. The duration of each service is varies from minutes to hours long and there is only one record for each service. My customer wants information about each hour of the day.

I came to power query as a potential tool because of its UNPIVOT feature. This may not be the best approach, but I think if I can create a column for each hour that a client was in their service I could ultimately create an individual record for each person's service for each hour.

Here is an example of the data I want to transform:

Client	Service	Start	End
A	XYZ	8:00	10:59
B	LMN	9:00	12:59

I want the following records:

Client	Service	Hour
A	XYZ	8:00
A	XYZ	9:00
A	XYZ	10:00
B	LMN	9:00
B	LMN	10:00
B	LMN	11:00
B	LMN	12:00

I tried to use the "Add Column", "Conditional Column" route, thinking I would create columns for each hour of operation and I would UNPIVOT the whole darn thing when I finished. Unfortunately, I couldn't figure out how to use an "AND" clause like I would in Excel to compare the start and end time of each hour.

Is there a better way to accomplish this task?

MarcelBeug · Feb 28, 2017

Query HoursOfTheDay that outputs a list with the hours of a day (0:00-23:00):

Code:

List.Times(#time(0,0,0),24,#duration(0,1,0,0))

In your main query:

Code:

    #"Added Custom" = Table.AddColumn(PreviousStep, "Hour", (Client) => List.Select(HoursOfTheDay, each _ >= Client[Start] and _ <= Client[End])),
    #"Expanded Hour" = Table.ExpandListColumn(#"Added Custom", "Hour"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Hour",{"Start", "End"})
in
    #"Removed Columns1"

"PreviousStep" is the step with your table.
The #Ädded Custom" step is first created via "Add Column" - "Custom Column". In the generated code, I replaced 1 "each" by "(Client) =>".
The "Expanded Hour" step is created by clicking the double arrows ico in the right upper corner of the column header.
The "Removed Columns1" is created by selecting the columns, right-click and choose "Remove Columns".

Power Query - Add Columns Conditional

Sherri

New member

MarcelBeug

New member