Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to get data cleaned up

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 :   ...
Need Help
  • Anonymous's avatar
    Anonymous
    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's avatar
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's avatar
    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