How to get data cleaned up

[Anonymous]

I have a table with multiple columns

How can I only get customer name when some line items have partner info followed by :

If I extract before: it also removes the customer name with no :

i.e. I only want to remove AT&T

[BA_Pete]

*EDIT* Ignore this answer, @Anonymous 's solution is the way to go. Much tidier 🙂

Hi @Anonymous ,

In Power Query, open a new blank query and paste this code over the default:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgyJKTUwMDILsVJwzE2tUIrVQRHzdPJFFwr2cPXxAQs6Ofp5K/i7KTj6ugZ5OjtCxYKcfRwjg5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customerAccount = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "customerAccount", Splitter.SplitTextByEachDelimiter({": "}, QuoteStyle.Csv, false), {"customerAccountSplit", "customerAccount"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter", null, each [customerAccountSplit], Replacer.ReplaceValue,{"customerAccount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"customerAccountSplit"})
in
    #"Removed Columns"

The steps I took:

1) Split original column by ": " delimiter

2) Replaced null values in split column 2 with values from split column 1

3) Removed split column 1

Pete

[Anonymous]

Pete

Thank you for a quick answer

Unfortunately, my skill level is not allowing this to work for me

Splitting the column is easy and can see null in many places

BUT

Embarrassingly I cannot see how to Open a new blank query

The replace function seems to replace one value with another, not from other columns

Sorry but I am very new to Power BI and was hoping this extract would be very easy

[Anonymous]

In Power Query, select the column and go to Transform Tab.

Click on Extract and select Text after delimeter.

Provide the input as below-

[Anonymous]

Sorry this gives the opposite to what I required 

I need the text after the delimiter and not to delete the customers without a delimiter

[Anonymous]

If you expand the advanced options and set the scan as from the end of the input, it should work the way to want. I verified this with a sample data

Before- 

After-

[Anonymous]

You can try below steps:

1. I have created a sample data with one row

2. Right click on header. Go to split column and choose 'by delimiter'

3. Fill the pop up window like below and click on okay

4. your data is now split

[Anonymous]

Sorry this is what I have been doing but it removes the original customer without a delimiter see attachment 

[Anonymous · Accepted solution]

In such case, I would add a colon to those values which don't have it at approriate place and then apply the transformation 

[Anonymous]

Was my mistake

Thank you, for sticking with me 🙂