Eplace the empty string to a blank value


#1

Hi,

How do I replace the empty string to a blank value so I use the the path function.

When I used the below function
Is Text = IF(ParentChild[Manager]=" ",ParentChild[Manager],BLANK())
it returns all blanks.

Thanks in advance
Elizabeth


#2

Elizabeth,

Let me take a look at this today. I’ll get back to you.

Guy


#3

Elizabeth,

How is the column ParentChild[Manager] formatted??

ISTEXT does the following: Checks whether a value is text, and returns TRUE or FALSE.

When applied to a column reference as expression, this functions tests the data type of the column, returning TRUE whether the column is a string data type and FALSE for any other data type.

If this doesn’t help can you provide more info or a pbix file

Guy


#4

@ElizabethTachjian
This looked familiar ( studying for 70-778? ). I found it easier to mess with the table in power query, and then just build the hierarchy formulas off of that table.

After loading the data into PQ:

  1. Replace Values on the Manager Column. Replace “” with null

  2. Create a custom column with the following formula:
    if [Manager] = null then
    [Employee]
    else
    [Manager]

This will fill in the blanks, which are now nulls, with the Employee field

  1. Then back in Data view in PBI, we can use this new manager column for all the hierarchy functions like Path, PathLength, Path Item #

I attached the PBIX so you can see the applied steps in PQ. Well I tried and it wont let me right now. So please let me know if you have any questions.

-Nick