Can I get the Same format of my data in excel sheet

[Waiam_hm]

Hello,

I am facing an issue that I have a column in my data the format of this column in dd:hh:mm and if I uploaded to Power query it have as type (any) when I want to change it to text or duration it give either in date time formate or error. could Any one help me in how to get that same format in excel ?

this is on excel:

this is if I leave it as (any) type in power query

this is if change the type to text:

 and if change it to duration it give me error.

[Anonymous]

Hi @Waiam_hm ,

After my testing, if you format the column as text in advance in Excel, the format imported into Power Query is also text and displays the same as in Excel.

Hope that can help you.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[ronrsnfld]

That format does not exist in Power Query.  And even in Excel, the "dd" portion cannot be greater than 31. 

So if you want to see that same format in Power Query as in Excel, you must construct a text string. You will no longer be able to use it as Duration or DateTime value.

eg:

Table1 in Excel

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    String = Table.TransformColumns(Source, {"Column1", each 
         Number.ToText(Number.IntegerDivide(Number.From(_),1),"00:") & DateTime.ToText(_,"HH:mm"), 
         type text
    })
in
    String

Result in PQ:

[Vijay_A_Verma]

Please copy few cells of Excel data in an Excel workbook and upload to either Onedrive or Google Drive and share the link here. 

[WaiamMalibari]

dd:hh:mm

00:20:04

00:00:05

00:00:05

00:00:06

00:00:14

00:00:33

00:00:12

00:00:04

00:00:00

00:00:10

00:00:05

[Vijay_A_Verma]

Actually, I need the values copied in an Excel sheet. When you paste it in the browser, it is losing underlying value. For example, there is a seconds component also which is getting missed out. I also need to see if you really have day component in your values or it is just a formatting. 

[Vijay_A_Verma]

Use this formula to convert it into duration in a custom column

Duration.From([Data]-#datetime(1899,12,31,0,0,0)))

[Waiam_hm]

it does not work gives me error when appling it