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

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

How to get data cleaned up | Microsoft Fabric Community

9 Replies

Anonymous
Not applicable
5 years ago
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
  Not applicable
  5 years ago
  Sorry this is what I have been doing but it removes the original customer without a delimiter see attachment
  - Anonymous
    Not applicable
    5 years ago
    In such case, I would add a colon to those values which don't have it at approriate place and then apply the transformation
BA_Pete
Super User
5 years ago
*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
  Not applicable
  5 years ago
  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
Not applicable
5 years ago
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
  Not applicable
  5 years ago
  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
    Not applicable
    5 years ago
    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-