Change Data Type for Specific Rows

[AragakiSora]

Hi!

I have a table that looks similar to this: 

Issue_Name	Module	Threshold_Critical	Threshold_Severe	Threshold_Normal
Number Change	1	20.0%	10.0%	NA
Number Change	2	0.0%	NA	NA
Number Change	3	-5.0%	-10.0%	NA
Number Change	4	-20.0%	-15.0%	NA
Number Change	5	0.0%	NA	NA
Number Change	6	0.0%	NA	NA
Number Change	7	-65.0%	-70.0%	-75.0%
Number Change	8	-95.0%	-90.0%	-85.0%
Number Change	9	0.0%	NA	NA
Number Change	10	0.0%	NA	NA
Number Change	11	0.0%	NA	NA
Number Change	12	0.0%	NA	NA
Corrupted Record	1	10	5	3
Corrupted Record	2	3	1	NA
Corrupted Record	3	3	1	NA
Corrupted Record	4	3	1	NA
Corrupted Record	5	3	1	NA
Corrupted Record	6	3	1	NA

When I connected the table to Power BI, the percentages are automatically transformed into decimals. I tried to recover the percentages by changing data type for the three columns, but the issue is that I don't want the thresholds for "Corrupted Record" to be transformed into percentages. 

This is how the table looks like in Query Editor: 

I am wondering if it is possible to change data type for a few rows or if there is any workarounds.

Thank you in advance!

[Ashish_Mathur · Accepted solution]

Hi,

I suggest that you use this M code to transform your data as shown in the image below

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"NA",null,Replacer.ReplaceValue,{"Threshold_Critical", "Threshold_Severe", "Threshold_Normal"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Issue_Name", "Module"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Issue_Name]), "Issue_Name", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Number Change", Percentage.Type}, {"Corrupted Record", type number}})
in
    #"Changed Type"

Hope this helps.

[lbendlin]

Data types cover the entire column. Your only options are to set everything to text, or to leave it at variant (ABC123).