Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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"]))
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading