Replacing records with specific number of leading spaces

[Anonymous]

Hello, Is it possible to search a record for a specific number of leading spaces and then replace them with a value (null or blank)?

I’m trying to identify which records in a column do NOT start with a space and then replace them with ‘null’ or a blank.  I’ve tried several different functions (i.e.  Text.At , Text.PositionOf, Text.Start, etc.) that seem like they might work but so far no luck (some of it, I admit, is my lack of proper syntax).  

The second step will be applying that same logic (hopefully) to identify records with 4 leading spaces and change their value to null or blanks. I also tried using REPLACE VALUES using "    ?*" (that's four blanks (bbbb?*)) but no luck. 

The diagram hopefully explains my goal to use this same logic to further parse the original column.

Thanks in advance for any help.

[Greg_Deckler]

How about:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY/BCsIwDIZfJfTsS+igN0EUvJQdsvrrwmozuvr+Kow25PR94YMkBOdVH248BEd0xwuVp4RtF0QDl6K18wXc4VQ0Rk2yC18+UvuS829MKZnXNaGJo6Ez2JTAswHRrYIXw1eZzH2zxAW5sZdtti2nt2Yjhv+z4xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Categories = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Categories", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Subcategory1", each if Text.StartsWith([Categories]," ") then [Categories] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Subcategory2", each if [Subcategory1] = null then null else if Text.StartsWith([Subcategory1], "  ") and not Text.StartsWith([Subcategory1], "    ")  then null else [Subcategory1]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Subcategory3", each if [Subcategory2] = null then null else if Text.StartsWith([Subcategory2], "    ") and not Text.StartsWith([Subcategory2], "      ")  then null else [Subcategory2])
in
    #"Added Custom2"

[Anonymous]

Hi Greg,

Thanks for the help.  I'm still new to PBI so I'm probably doing something wrong that's really basic.  I've placed your code in a new column, a new measure, and a new query.  The column and the measure both give me a syntax error  " 'Source' is incorrect......  When I create a new query and use the advanced editor, I get an Eof error

[Greg_Deckler]

@Anonymous - The code I pasted was a full Query code. So, start with a Blank Query and then paste in the code in Advanced Editor. I just used Enter Data to create the data and then the other steps built out your splitting logic.

[Anonymous]

Thanks Greg,

I tried that but got the Eof error.  I'll try it again and see if I can get it to work.  I have one more day of PBI under my belt now so maybe I'll see what my issue was.

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Here is how you can distinguish between 4 and 8 leading spaces

Try this Column

Sub Category 2 =
SWITCH (
    TRUE (),
    LEFT ( FoodTable[SubCategory 1], 5 ) = "     ", FoodTable[SubCategory 1],
    LEFT ( FoodTable[SubCategory 1], 4 ) = "    ", "NULL",
    FoodTable[SubCategory 1]
)

[Anonymous]

Hi Zubair, 

Thanks again.  This worked well and I was able to apply it to each column by adjusting the number of leading spaces in the statement.  

BTW, I also found another way to make this work using the GUI.   I got lucky and the indentations  in my data are consistently based on 4 more spaces with each indentation,  For example, I have the following indentations, 0, 4, 8, 12, 16.  I was able to Split the column using a Custom delimeter of 4 spaces.  

Your solution was good though because now I have a way to adjust to a variety of leading spaces.  Thanks!

[Zubair_Muhammad]

Hi @Anonymous

You can use a calculated column in DAX as well

Column =
IF ( LEFT ( FoodTable[Categories], 1 ) = " ", "null", FoodTable[Categories] )

[Anonymous]

Thanks Zubair.  That works for the first subcategory column but it doesn't look like I can distinguish between 4 leading spaces and 8 leading spaces.  I tried changing the number of characters from 1 to a 4 and to an 8 (because it looks like that how you specify the number of leading characters) but it doesn't seem to have an effect.