Solved: Create Hour from Text

Anonymous · ‎05-12-2022

Hi,

what I am trying to do is to create a new column (measure) that will calculate the differences between pick begin AND pick end. The result should be in minutes.

The format is like below:

pick begin	pick end	result
1145	1218	33
2110	2125	15
1907	1907	0
0710	0710	0
0321	0333	2
1621	1634	13
1334	1401	27
0135	0147	12
2336	2336	0

tamerj1 · ‎05-13-2022

@Anonymous
This is not my measure. You can also try by defining a new column as per the calculated column code that I've provided

DEFINE
COLUMN 'Pick Order'[Column Result] =
VAR PickBegin =
    VALUE ( RIGHT ( 'Pick Order'[PICK Time pick begin], 2 ) ) + 60 * VALUE ( LEFT ( 'Pick Order'[PICK Time pick begin], 2 ) )
VAR PickEnd =
    VALUE ( RIGHT ( 'Pick Order'[PICK Time pick end], 2 ) ) + 60 * VALUE ( LEFT ( 'Pick Order'[PICK Time pick end], 2 ) )
RETURN
    PickEnd - PickBegin

EVALUATE
SUMMARIZECOLUMNS (
    'Pick Order'[PICK Time pick begin],
    'Pick Order'[PICK Time pick end],
    'Pick Order'[Column Result]
)

View solution in original post

Anonymous · ‎05-13-2022

Thank you all for your reply. I am using ReportBuilder, testing in DaxStudio. Working on PowerBI DataSets.

And does not matter which of your solution I will choose I am receiving errors like:

"The result set of a query to external data source has exceeded the maximum allowed size"

DAX code:

DEFINE

MEASURE 'Pick Measures'[Time] =

VAR _hourbegin =
    LEFT ( SELECTEDVALUE (  'Pick Order'[PICK Time pick begin]), 2 )
VAR _minbegin =
    RIGHT ( SELECTEDVALUE (  'Pick Order'[PICK Time pick begin] ), 2 )
VAR _hourend =
    LEFT ( SELECTEDVALUE ( 'Pick Order'[PICK Time pick end] ), 2 )
VAR _minend =
    RIGHT ( SELECTEDVALUE ( 'Pick Order'[PICK Time pick end] ), 2 )
VAR _timebegin = _hourbegin * 60 + _minbegin
VAR _timeend = _hourend * 60 + _minend
RETURN
    _timeend - _timebegin

EVALUATE
SUMMARIZECOLUMNS (

    'Pick DC'[PDC Number],
    'Pick Date'[PD Date],
    'Pick Order'[PICK Order number],
    'Pick Order'[PICK Order pickgroup],
    'Sales Order'[SO Order Number],
    'Material'[MAT Temperature Zone Description],
     'Material'[MAT Business ID],
    'Pick Order'[PICK Time pick begin],
    'Pick Order'[PICK Time pick end],
    'Employee'[EMPL Name],
    FILTER (
        VALUES ( 'Pick DC'[PDC Country] ),
        'Pick DC'[PDC Country] = "USA"
    ),
    FILTER (
        VALUES ( 'Pick DC'[PDC Number] ),
        'Pick DC'[PDC Number] <> 100
    ),
    FILTER (
        'Pick Date',
        'Pick Date'[PD Date]
            = TODAY () - 1
    ),

    "Picked Quantity", [Picked Quantity],
    "Picked Net Weight (Kg)", [Picked Net Weight (Kg)],
    "Picked Gross Weight (Kg)", [Picked Gross Weight (Kg)],
    "Time",'Pick Measures'[Time]

)

tamerj1 · ‎05-12-2022

Hi @Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/OmLvgOdd6iXtXTbt

For a calculated column

Result = 
VAR PickBegin =
    VALUE ( RIGHT ( Timing[ pick begin ], 2 ) ) + 60 * VALUE ( LEFT ( Timing[ pick begin ], 2 ) )
VAR PickEnd =
    VALUE ( RIGHT ( Timing[ pick end ], 2 ) ) + 60 * VALUE ( LEFT ( Timing[ pick end ], 2 ) )
RETURN
    PickEnd - PickBegin

For a measure

Result Measure = 
VAR PickBegin =
    SUMX (
        Timing,
        VALUE ( RIGHT ( Timing[ pick begin ], 2 ) ) + 60 * VALUE ( LEFT ( Timing[ pick begin ], 2 ) )
    )
VAR PickEnd =
    SUMX (
        Timing,
        VALUE ( RIGHT ( Timing[ pick end ], 2 ) ) + 60 * VALUE ( LEFT ( Timing[ pick end ], 2 ) )
    )
RETURN
    PickEnd - PickBegin

Anonymous · ‎05-13-2022

Thank you all for your reply. I am using ReportBuilder, testing in DaxStudio. Working on PowerBI DataSets.

And does not matter which of your solution I will choose I am receiving errors like:

"The result set of a query to external data source has exceeded the maximum allowed size"

DAX code:

DEFINE

MEASURE 'Pick Measures'[Time] =

VAR _hourbegin =
    LEFT ( SELECTEDVALUE (  'Pick Order'[PICK Time pick begin]), 2 )
VAR _minbegin =
    RIGHT ( SELECTEDVALUE (  'Pick Order'[PICK Time pick begin] ), 2 )
VAR _hourend =
    LEFT ( SELECTEDVALUE ( 'Pick Order'[PICK Time pick end] ), 2 )
VAR _minend =
    RIGHT ( SELECTEDVALUE ( 'Pick Order'[PICK Time pick end] ), 2 )
VAR _timebegin = _hourbegin * 60 + _minbegin
VAR _timeend = _hourend * 60 + _minend
RETURN
    _timeend - _timebegin

EVALUATE
SUMMARIZECOLUMNS (

    'Pick DC'[PDC Number],
    'Pick Date'[PD Date],
    'Pick Order'[PICK Order number],
    'Pick Order'[PICK Order pickgroup],
    'Sales Order'[SO Order Number],
    'Material'[MAT Temperature Zone Description],
     'Material'[MAT Business ID],
    'Pick Order'[PICK Time pick begin],
    'Pick Order'[PICK Time pick end],
    'Employee'[EMPL Name],
    FILTER (
        VALUES ( 'Pick DC'[PDC Country] ),
        'Pick DC'[PDC Country] = "USA"
    ),
    FILTER (
        VALUES ( 'Pick DC'[PDC Number] ),
        'Pick DC'[PDC Number] <> 100
    ),
    FILTER (
        'Pick Date',
        'Pick Date'[PD Date]
            = TODAY () - 1
    ),

    "Picked Quantity", [Picked Quantity],
    "Picked Net Weight (Kg)", [Picked Net Weight (Kg)],
    "Picked Gross Weight (Kg)", [Picked Gross Weight (Kg)],
    "Time",'Pick Measures'[Time]

)

tamerj1 · ‎05-13-2022

@Anonymous
This is not my measure. You can also try by defining a new column as per the calculated column code that I've provided

DEFINE
COLUMN 'Pick Order'[Column Result] =
VAR PickBegin =
    VALUE ( RIGHT ( 'Pick Order'[PICK Time pick begin], 2 ) ) + 60 * VALUE ( LEFT ( 'Pick Order'[PICK Time pick begin], 2 ) )
VAR PickEnd =
    VALUE ( RIGHT ( 'Pick Order'[PICK Time pick end], 2 ) ) + 60 * VALUE ( LEFT ( 'Pick Order'[PICK Time pick end], 2 ) )
RETURN
    PickEnd - PickBegin

EVALUATE
SUMMARIZECOLUMNS (
    'Pick Order'[PICK Time pick begin],
    'Pick Order'[PICK Time pick end],
    'Pick Order'[Column Result]
)

Anonymous · ‎05-17-2022

Thank you, that is perfect!

CNENFRNL · ‎05-12-2022

It's supposed to use PQ to conduct such a data shaping task.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jc3BDcAwCAPAXXj3gTFJ2lmi7L9GY3iZkxHsbUAOewyB186zLQC/DsQo4/OlXiH7qr6izIBMsvdnGZPZ5h2u09H7oP45su8FOfVPcc4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"pick begin" = _t, #"pick end" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pick begin", Int64.Type}, {"pick end", Int64.Type}}),

    TotalMinutes = Table.AddColumn(#"Changed Type", "Total Minutes",
        each let
                begin=#time(Number.IntegerDivide([pick begin],100), Number.Mod([pick begin],100),0),
                end = #time(Number.IntegerDivide([pick end],100), Number.Mod([pick end],100),0)
            in Duration.TotalMinutes(end-begin)
    )
in
    TotalMinutes

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous · ‎05-13-2022

Hi, thank you. I am not able to use PQ

Jihwan_Kim · ‎05-12-2022

Hi,

Please check the below measure and the attached pbix file.

Result measure: =
VAR _hourbegin =
    LEFT ( SELECTEDVALUE ( Data[pick begin] ), 2 )
VAR _minbegin =
    RIGHT ( SELECTEDVALUE ( Data[pick begin] ), 2 )
VAR _hourend =
    LEFT ( SELECTEDVALUE ( Data[pick end] ), 2 )
VAR _minend =
    RIGHT ( SELECTEDVALUE ( Data[pick end] ), 2 )
VAR _timebegin = _hourbegin * 60 + _minbegin
VAR _timeend = _hourend * 60 + _minend
RETURN
    _timeend - _timebegin

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

Greg_Deckler · ‎05-12-2022

@Anonymous Maybe this and make sure you set the Data type to Whole number:

result = (TIME(LEFT([pick end],2), RIGHT([pick end],2),0) - TIME(LEFT([pick begin],2), RIGHT([pick begin],2),0)) * 60 * 24

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Anonymous · ‎05-13-2022

Thank you all for your reply. I am using ReportBuilder, testing in DaxStudio. Working on PowerBI DataSets.

And does not matter which of your solution I will choose I am receiving errors like:

"The result set of a query to external data source has exceeded the maximum allowed size"

DAX code:

DEFINE

MEASURE 'Pick Measures'[Time] =

VAR _hourbegin =
    LEFT ( SELECTEDVALUE (  'Pick Order'[PICK Time pick begin]), 2 )
VAR _minbegin =
    RIGHT ( SELECTEDVALUE (  'Pick Order'[PICK Time pick begin] ), 2 )
VAR _hourend =
    LEFT ( SELECTEDVALUE ( 'Pick Order'[PICK Time pick end] ), 2 )
VAR _minend =
    RIGHT ( SELECTEDVALUE ( 'Pick Order'[PICK Time pick end] ), 2 )
VAR _timebegin = _hourbegin * 60 + _minbegin
VAR _timeend = _hourend * 60 + _minend
RETURN
    _timeend - _timebegin

EVALUATE
SUMMARIZECOLUMNS (

    'Pick DC'[PDC Number],
    'Pick Date'[PD Date],
    'Pick Order'[PICK Order number],
    'Pick Order'[PICK Order pickgroup],
    'Sales Order'[SO Order Number],
    'Material'[MAT Temperature Zone Description],
     'Material'[MAT Business ID],
    'Pick Order'[PICK Time pick begin],
    'Pick Order'[PICK Time pick end],
    'Employee'[EMPL Name],
    FILTER (
        VALUES ( 'Pick DC'[PDC Country] ),
        'Pick DC'[PDC Country] = "USA"
    ),
    FILTER (
        VALUES ( 'Pick DC'[PDC Number] ),
        'Pick DC'[PDC Number] <> 100
    ),
    FILTER (
        'Pick Date',
        'Pick Date'[PD Date]
            = TODAY () - 1
    ),

    "Picked Quantity", [Picked Quantity],
    "Picked Net Weight (Kg)", [Picked Net Weight (Kg)],
    "Picked Gross Weight (Kg)", [Picked Gross Weight (Kg)],
    "Time",'Pick Measures'[Time]

)

Create Hour from Text

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024