Power Query not Showing Date Properly After Transposed

user_13749193 0
Viewed 42

I have the following query table as shown on the first image. But I want that query transposed to horizontal as I want. So I transposed that query table by transposing like on the second image. It is looks good on the power query editor, but the problem is when it appear on the excel sheet, it becomes number like as shown on the third picture. And I don't like it, I want to that date is on the date format, not on the number format.

I actually can select all the second row and change the format to date, but i don't want to do that, I want the formatting automatically changed by the power query itself.

Image 1

Image 2

Image 3

powerpivot powerbi powerquery excel
1 Answers

You have to add resp. change the M-Code of the query. Either you add a step or you take the following M-Code as an example how to change the data type of all columns to date

let
    src = Excel.CurrentWorkbook(){[Name="table1"]}[Content],
    transTbl = Table.Transpose(src),
    chTypeAll = Table.TransformColumnTypes(transTbl,List.Transform(Table.ColumnNames(transTbl),each {_,type date}))
in
    chTypeAll

The last step named chTypeAll will change the data type of all columns to date. You can add this as a step by pasting the corresponding line into the formula bar.

enter image description here

The yellow marked text should correspondend to the name of the previous step

steps

In case you do not see the formula bar go to View and mark formula bar

formula bar

user_66009400
Related