CODEWITHSUNDEEP
powerbidaxpowerquery
Deepak Kumar
Deepak Kumar

Reputation: 17

How to use first and last option to create new variable in Power Bi?

I want to create a new variable based on the first and last records of each ID variable. A sample reference table is given below that will make it easier to understand the problem.

Table: -

enter image description here

    pkBookingItemID StartTime   EndTime BusinessStartTime   BusinessEndTime
    3417              13:45       14:00        09:00           17:00
    3417              13:45       14:00        09:00           17:00
    3417              13:45       14:00        09:00           17:00
    3417              13:45       14:00        09:00           17:00
    3418              10:00       15:00        09:00           16:00
    3418              10:00       15:00        09:00           16:00
    3418              10:00       15:00        09:00           16:00

I want to create variables New_BusinessStartTime and New_BusinessEndTime based on pkBookingItemID occurance.

Desired output table:-

enter image description here

pkBookingItemID StartTime   EndTime BusinessStartTime   BusinessEndTime New_BusinessStartTime   New_BusinessEndTime
3417               13:45      14:00           09:00              17:00           13:45              17:00
3417               13:45      14:00           09:00              17:00           09:00              17:00
3417               13:45      14:00           09:00              17:00           09:00              17:00
3417               13:45      14:00           09:00              17:00           09:00              14:00
3418               10:00      15:00           09:00              16:00           10:00              16:00
3418               10:00      15:00           09:00              16:00           09:00              16:00
3418               10:00      15:00           09:00              16:00           09:00              15:00

I have to achieve this by M query in Power BI. I tried so far to get the desired table but could not get any success.

New_BusinessStartTime  =
    if [pkBookingItemID] = [pkBookingItemID.First]
    then [StartTime] else [BusinessStartTime]
    
New_BusinessEndTime =
    if [pkBookingItemID] = [pkBookingItemID.Last]
    then [EndTime] else [BusinessEndTime]

.First and .Last parts are not giving results as I thought initially. Can anyone help out with the problem or offer a suggestion to solve this problem?

Upvotes: 0

Views: 430

Answers (3)

RF1991
RF1991

Reputation: 2265

Are you using import mode in power query? Click on your table and copy your table (for first and last row) then right click on your specific column then 'remove other column' copy this table(for first and last time). Then right-click on the table option beside your column and then choose 'Keep Top rows' AND ' Keep bottom rows'. Then use append queries to union them in one table and use them as parameter.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

You can do this within the Table.Group function by creating a new table that grabs the appropriate values for the first and last entry.

eg:

Original Data
enter image description here

M code

...
//Group rows by ID and generate the new start/end columns
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"pkBookingItemID"}, {
        {"NewTimes", each Table.FromColumns(
            Table.ToColumns(_) &
            {List.ReplaceRange([BusinessStartTime],0,1,{[StartTime]{0}}),
            List.ReplaceRange([BusinessEndTime],Table.RowCount(_)-1,1,{[EndTime]{Table.RowCount(_)-1}})},
            Table.ColumnNames(_) & {"NewBusinessStartTime","NewBusinessEndTime"})
            }
        }),
    #"Expanded NewTimes" = Table.ExpandTableColumn(#"Grouped Rows", "NewTimes", {"StartTime", "EndTime", "BusinessStartTime", "BusinessEndTime", "NewBusinessStartTime", "NewBusinessEndTime"}, {"StartTime", "EndTime", "BusinessStartTime", "BusinessEndTime", "NewBusinessStartTime", "NewBusinessEndTime"})
in
    #"Expanded NewTimes"

Results
enter image description here

Upvotes: 0

Ryan B.
Ryan B.

Reputation: 3665

The chief problem here is that there's nothing that distinguishes the 'order' of your rows. Why should that top '3417' row be considered as the first instead of the row that's below it? I know that it seems obvious to us ... one row is listed first, that's the first one. But that's not quite good enough for the machine's brain. It needs something in the data to help it tell the rows apart.

So, we give it something to work with. First, we assume that the rows are sorted in the manner you think is correct. Then, we will add an index number that goes from 1 to whatever, and identify which of these is the biggest or smallest in each ItemId group. This will tell us first and last rows.

Then we use the conditional logic you've already worked out. Instead of testing against pbBookingItemId.First, we'll just check to see if our index matches the 'first' or 'last' index in each group.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYxNFfSUTI0tjIxBdEmVgYGQNoSQhmag+hYnQFVZwESMIBKmKKqM6OJulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [pkBookingItemID = _t, StartTime = _t, EndTime = _t, BusinessStartTime = _t, BusinessEndTime = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"pkBookingItemID"}, {{"AllRows", each _, type table [pkBookingItemID=nullable text, StartTime=nullable text, EndTime=nullable text, BusinessStartTime=nullable text, BusinessEndTime=nullable text, Index=number]}, {"MinIndex", each List.Min([Index]), type number}, {"MaxIndex", each List.Max([Index]), type number}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"pkBookingItemID", "StartTime", "EndTime", "BusinessStartTime", "BusinessEndTime", "Index"}, {"pkBookingItemID.1", "StartTime", "EndTime", "BusinessStartTime", "BusinessEndTime", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "New_BusinessStartTime", each if [Index] = [MinIndex] then [StartTime] else [BusinessStartTime]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "New_BusinessEndTime", each if [Index] = [MaxIndex] then [EndTime] else [BusinessEndTime])
in
    #"Added Custom1"

Paste the code above into an advanced query editor and take a look. You'll see how the grouped rows and "AddIndexColumn" function work together help us choose a first and last row.

Then, we use your conditional logic to build out the 'new' business start or end times.

enter image description here

Hope it helps.

Upvotes: 1

Related Questions