How to remove leading YYYY-MM-DD and format the data type in powerbi

0
votes

I am trying to format the column to data type "duration" in PowerBI. In order to do this, I need to remove the YYYY-MM-DD which appeared after the data load. Im guessing that the inconsistencies are due to some values being in this format: -3:34 and orders in this: 10:43:00

This is how the column looks in powerbi:
image1
This is how it looks in Excel before the import to powerbi:
image2

I have tried to "replace values" in powerquery, which gives me this result:

image 3

Once I change the datatype to "duration" and "apply and load" it gives me this result:

image4

I am expecting the result to be -02:03, 03:01. Negative values indicate an early delivery and positive values indicate a late delivery. e.g. 2h 3 min early or 3h 1 min late. Column needs to be in some form of time data type since I am planning to do calculations etc.

Any suggestions on how to solve this? Some form of table.replace()? I tried table.replace but got errors. the dax function Substitute works but I want to be able to delete one of the columns since the substitute will give me a new column.

1
powerbidaxpowerquerym
What is the data column type in your excel? text? - Aldert
No, its time. But once its imported in pbi it becomes text. - n00b

1 Answers

0
votes

I have done a test with a column in Excel (which is time). I imported in Power-Bi using the following query language (Please note the type duration in last step):

    let
Source = Excel.Workbook(File.Contents("C:\...\Test.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HHMM_DELAYED", type duration}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Seconds", each Duration.TotalSeconds([HHMM_DELAYED]), type number),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "DurationText", each Duration.ToText([HHMM_DELAYED]))
in
    #"Added Custom1"

This worked perfecly fine for me, see result below:

enter image description here