powerquerypowerbi-desktopm

How to convert text into number in Power Query?


I split the time and minutes from the timedate into text; now I want to convert back into a number (the minutes need to be padded with a zero).

Filtertime = Number.From(
    Text.From(Time.Hour([Timestamp_Time])) &
    Text.PadStart(Text.From(Time.Minute([Timestamp_Time])), 2, "0" ))

The text column I want to transform is below:

Timestamp_Time
2:00:00 PM
2:01:00 PM
2:02:00 PM

The transformed column should read: 1400, 1401, and 1402. For reference, I am attempting to follow the solution in the following link: https://community.fabric.microsoft.com/t5/Desktop/Slider-Slicer-that-Includes-Time-Not-just-Date/td-p/2572000

Currently I receive the following error: "Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"


Solution

  • That formula works if you use it in a Custom Column:

    enter image description here

    You've used it directly as a Power Query step and hence the error.
    In Power Query it should look like:

    #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"YOUR PREVIOUS STEP", "Filtertime", each Number.From(
        Text.From(Time.Hour([Timestamp_Time])) &
        Text.PadStart(Text.From(Time.Minute([Timestamp_Time])), 2, "0" ))), {{"Filtertime", Int64.Type}})
    

    A cleaner formula would be:

    Number.From(Time.ToText([Timestamp_Time], [Format = "HHmm"]))