Confused as to why some values in my column are scientific notation after UNION on multiple tables

[stribor45]

In Power Query, I have a few tables. All tables have the same number of columns and each has the same data types. The column of interest here is column A in each of these tables, and this column's data type is duration.  In my reports, I use UNION to put all of these tables together into one virtual table. Let's call this table Table X. When i look at column A of Table X, I find that some of the values are in scientific notation. 

For example for this value "0:21:34" in my Table X I am seeing this value "1.49768518518519E-02" which is correct if you convert it but I am confused why am I seeing this? additionally, only some of these values are shown in scientific notation while others are shown properly in decimal. 

Anyone has any suggestions how to fix this?

[Greg_Deckler]

@stribor45 Not sure you need to worry about it, you should be able to control the formatting once they are loaded and visualized.

[stribor45]

I am having some trouble calculating the average on this column. Column A should be type decimal but for some reason is text. When I try to change the type to decimal using column tools I get this error 

[Greg_Deckler]

@stribor45 You might have to try converting to a duration and then to decimal. It's tough to say without sample data and only bits and pieces of information.

[stribor45]

I know but in power query column A is of type duration for each table. there is no type "duration" in DAX

[Greg_Deckler]

@stribor45 Correct, durations become decimals when imported into a semantic model. Same thing with dates and times technically, they are all just decimal numbers.

[stribor45]

Yes I was aware of that. what do you recommend I do in order to fix this error?

[Greg_Deckler]

@stribor45 It's difficult to know specifically but it appears as if somewhere in the process the values are being converted to Text. So, I would convert them to Duration as a final step and then they should import as decimal numbers in the semantic model. I tested your format and it converts from Text to Duration just fine.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzNLAyNLIyNFaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type duration}})
in
    #"Changed Type"

[stribor45 · Accepted solution]

I was able to create a calculated table by using UNION and SELECTCOLUMN. I picked only the columns i needed and it worked fine. No data type issues. 

After UNION wrong data 

Result Table =
UNION (
    SELECTCOLUMNS (
        Table1,
        "Column1", Table1[Column1],
        "Column2", Table1[Column2],
        "Column3", Table1[Column3]
    ),
    SELECTCOLUMNS (
        Table2,
        "Column1", Table2[Column1],
        "Column2", Table2[Column2],
        "Column3", Table2[Column3]
    )
)