Edit query not changing format

[Bando]

I'm using edit query to transform data from and Excel table to get the correct formatts for my work. You can see from the query below that the formatting changes seem to be made but when I look at the table view in Power Bi desktop the transformations have not taken place? My data is formatted in an Excel table.

Please help!

let
    Source = Excel.Workbook(File.Contents("C:\Users\d7780664\Downloads\DORIS.xls.xlsx"), null, true),
    Tech_Weekly_Table = Source{[Item="Tech_Weekly",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tech_Weekly_Table,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Removed Top Rows" = Table.Skip(#"Promoted Headers",1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Week Commencing", type date}, {"Technology CSO Counted SLA%", Percentage.Type}, {"Technology MSO Counted SLA%", Percentage.Type}, {"Technology P1 Counted SLA% (including CSO/MSO)", Percentage.Type}, {"Technology High Priority SLA% - CSO/MSO & P1", Percentage.Type}, {"Technology P2 SLA%", Percentage.Type}, {"Technology P2 Reactive SLA%", Percentage.Type}, {"Technology P2 Proactive SLA%", Percentage.Type}, {"Technology P3 SLA%", Percentage.Type}, {"Technology P4 SLA%", Percentage.Type}, {"Technology Change Success %", Percentage.Type}, {"Technology Transactional NPS", Percentage.Type}, {"Technology Request SLA%", Percentage.Type}, {"P1 (Counted, not counted, MSO and CSO) SLA%", Percentage.Type}, {"Technology Incident SLA%", Percentage.Type}, {"Technology Incident & Request SLA%", Percentage.Type}, {"FTF", Percentage.Type}, {"YTD Cumulative FTF", Percentage.Type}, {"Change Effectiveness", Percentage.Type}, {"TSC Logged", Int64.Type}, {"NPS Returns", Int64.Type}, {"NPS Detractors", Int64.Type}, {"NPS Promoters", Int64.Type}, {"Changes not Abandoned", Int64.Type}, {"TSC Closed", Int64.Type}, {"Volume for change effectiveness measure", Int64.Type}, {"Changes Causing Major Incident (from Problem tickets)", Int64.Type}, {"Week Number_1", Int64.Type}, {"Technology Change Urgent", Int64.Type}, {"Technology Incident & Request Volume", Int64.Type}, {"Technology Incident Volume", Int64.Type}, {"P1 (Counted, not counted, MSO and CSO)", Int64.Type}, {"Technology Request Volume", Int64.Type}, {"Technology Problems Caused By Change", Int64.Type}, {"Technology Change Volume", Int64.Type}, {"Technology P4 Volume", Int64.Type}, {"Technology P3 Volume", Int64.Type}, {"Technology P2 Proactive Volume", Int64.Type}, {"Technology P2 Reactive Volume", Int64.Type}, {"Technology P2 Volume", Int64.Type}, {"Technology High Priority Volume - CSO/MSO & P1", Int64.Type}, {"Technology P1 Counted Volume (including CSO/MSO)", Int64.Type}, {"Technology MSO Counted Volume", Int64.Type}, {"Technology CSO Counted Volume", Int64.Type}, {"Week Number", Int64.Type}})
in
    #"Changed Type1"

[MarcelBeug]

Probably the formats were changed, but the percentage data type in Power Query does not exist in the data model.

So e.g. 99% in the Query Editor becomes 0.99 in the data model.

Maybe the data type in the data model was adjusted to whole numbe, causing the 0.99 convert to 1.

[Bando]

Hi thanks for the quick reply but I'm not too sure I understand? What do I need to do to ensure the formats stay changed?

Cheers

[MarcelBeug]

Do you mean the percentages?

[Bando]

Yeah the percentages. Thanks

[MarcelBeug · Accepted solution]

OK, the data type Percentage doesn't exist in the data model, but you can use decimal number, with format percentage.

Alternatively, if you want e.g. 25% to show up as 25 in the data model, you need to multiply the values by 100.

[lkrombach]

Having a similar issue with the query formatting functionality --> not with percentages but with Time (duration). I'm seeing that I have the capability to transform the column "data type" to duration but it still will not "apply" to the data model.