Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I have some data from a client with a time column, only it is strange. Basically, it goes HHMMSS, however, if the beginning digits are zero, they disappear. EX - if the time should be 012233, it instead shows 12233. If it should be 001122, it shows 1122. So I'm not sure how to convert this. I'm trying a text.end() in Power Query, but not sure it'll work the way I want it to.
Here's what I'm trying, but it's saying it can't find the name "Time" (and I checked, that's what the column is called) -
Custom Time =
if Text.Length([Time]) = 6 then [Time] else
if Text.Length(Time) = 5 then Text.Combine({"0",[Time]}) else
if Text.Length(Time) = 4 then Text.Combine({"00",[Time]}) else
if Text.Length(Time) = 3 then Text.Combine({"000",[Time]}) else
if Text.Length(Time) = 2 then Text.Combine({"0000",[Time]}) else
if Text.Length(Time) = 1 then Text.Combine({"00000",[Time]}) else
"Help"
Solved! Go to Solution.
Hi @Thigs
First off, it looks like you are missing the square brackets on some of your references to the Time column, which is likely the cause of the error.
Secondly, you can simplify the code by using Text.PadStart.
The code in the Custom Column dialog box would look like:
Custom Time
= Text.PadStart( [Time], 6, "0" )
I'm assuming Time is a text column by the way.
Regards,
Owen
They disappear because PQ wants to treat them automatically as numbers. Just remove the automatic type recognition piece from the code in the right pane (where the steps are defined) and set your own types for all the columns. For this column the type should be 'text', obviously. There's no need to type any fancy formulas to 'regain' the zeros.
You mean the first step where it says source? I already tried that, it isn't working. I changed the original step to make it text, not numeric, it still isn't happy.
First, make sure the column from the source is imported into PQ as text, not a number. I don't know what you import this from but if this is, say, SQL Server, just make sure this column in the table/view is really text (varchar/nvarchar).
Hi @Thigs
First off, it looks like you are missing the square brackets on some of your references to the Time column, which is likely the cause of the error.
Secondly, you can simplify the code by using Text.PadStart.
The code in the Custom Column dialog box would look like:
Custom Time
= Text.PadStart( [Time], 6, "0" )
I'm assuming Time is a text column by the way.
Regards,
Owen
This worked! Thanks a bunch, I hadn't heard of that formula but it worked perfectly!
User | Count |
---|---|
53 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
114 | |
44 | |
44 | |
28 | |
22 |