Search code examples
excelpowerbipowerquerypowerpivot

Power Query not Showing Date Properly After Transposed

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

Solution

  • 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