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.
CodePudding user response:
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.
The yellow marked text should correspondend to the name of the previous step
In case you do not see the formula bar go to View and mark formula bar