Excel Power Query and Pivot Tables-CodePudding

In the Excel Power Query Editor, I have a field whose values I'm converting. The code looks like this

if [Field] = "a" then "abcd" else if [Field] = "b" then "efg" 
else if [Field]="  " then "  "

The problem is that [Field] could have just 2 spaces, like this " ", and I want to keep those two spaces, but when I see that field in a pivot table the spaces are gone and the field value is essentially blank. Pivot tables seem to trim the field's value. How can I prevent that trimming so that the two-space value is retained?

CodePudding user response：

If character does not necessarily need to be space character then you could use other unicode characters that look like space: https://www.compart.com/en/unicode/category/Zs

For example: https://www.compart.com/en/unicode/U 2000

You just copy character and paste it to PQ.

CodePudding user response：

The ANSI character code 160 can do this for you. AKA "the evil char", sometimes cheats us by insisting on being where it shouldn't be (in place of a blank). Then create a variable called, say, CharCode160, and assign the character Chr(160) to it. This is the M code to guide you through your solution.

Please note that the source table is named "Table1" and the field involved has the column title "TheField".

It will be useful to add a routine - or modified code - that counts how many blanks there are in the field, to make the correct replacement.

let
CharCode160 = Character.FromNumber(160),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TheField", type text}}),
    #"Conditional Column Added" = Table.AddColumn(#"Changed Type", "Custom", each if [TheField] = "a" then "abcd" else if [TheField] = "b" then "efg" else if [TheField] = "  " then CharCode160 & CharCode160 else null)
in
    #"Conditional Column Added"