Replace Values

[KarlWat]

Hi all,

I want to see if you guys have any feedback and help!

So, I get data from a system that is fed manually, which means that I need to clean up the data before I can really work with it. Below, you see more or less the data I get, over 3 different columns. I need that data to be in a unique row, so the only way I have found to do this is to modify the text inside the columns so I can then split them into rows (same index number). 

This does the job, but it has been bothering me that I have to do all those steps to replace values instead of doing in in one step...

For Context (on the left a simplified source table, on the right the outcome I need:)

and here my whole code:

let

    Source = Excel.CurrentWorkbook(){[Name="Outbound"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Number 1", type text}, {"Number 2", type text}, {"Number 3", type text}}),

    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

    #"Trimmed Text" = Table.TransformColumns(#"Removed Blank Rows",{{"Number 1", Text.Trim, type text}, {"Number 2", Text.Trim, type text}, {"Number 3", Text.Trim, type text}}),

    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text"," ","",Replacer.ReplaceText,{"Number 1", "Number 2", "Number 3"}),

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","/","",Replacer.ReplaceText,{"Number 1", "Number 2", "Number 3"}),

    #"Merged Columns" = Table.CombineColumns(#"Replaced Value1",{"Number 1", "Number 2", "Number 3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),

    #"Replaced Value2" = Table.ReplaceValue(#"Merged Columns","0VIN","0/VIN",Replacer.ReplaceText,{"Merged"}),

    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","1VIN","1/VIN",Replacer.ReplaceText,{"Merged"}),

    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","2VIN","2/VIN",Replacer.ReplaceText,{"Merged"}),

    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","5VIN","5/VIN",Replacer.ReplaceText,{"Merged"}),

    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","4VIN","4/VIN",Replacer.ReplaceText,{"Merged"}),

    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","8VIN","8/VIN",Replacer.ReplaceText,{"Merged"}),

    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","6VIN","6/VIN",Replacer.ReplaceText,{"Merged"}),

    #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","9VIN","9/VIN",Replacer.ReplaceText,{"Merged"}),

    #"Added Index" = Table.AddIndexColumn(#"Replaced Value9", "Index", 1, 1),

    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Merged"}),

    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Reordered Columns", {{"Merged", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),

    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", type text}})

in

    #"Changed Type1"

I tried: 

#"AllReplace" = [#"0VIN" = "0/VIN",#"0VIN" = "0/VIN",#"0VIN" = "0/VIN",#"0VIN" = "0/VIN"],

#"ReplacedValue" = Table.TransformColumns(#"Merged Columns", {{Merged, each Record.FieldOrDefault(AllReplace,_,_)}})

in

#"Replaced Value"

But of course, this did not work because I only need a partial match in the value to be replaced. Any pointers to how to approach this - and yes, I am a newbie, so any pointers to resources also are greatly appreciated 🙂

[v-angzheng-msft]

Hi,  @KarlWat 

Is it the steps above that bothers you? Why not try to replace "0VIN" with "VIN" like below:

 #"Replaced Value" = Table.ReplaceValue(#"Merged Columns","VIN","/VIN",Replacer.ReplaceText,{"Merged"})

 Then, remove redundant prefixes as needed.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KarlWat]

Hi Zeon, 

Thanks for your answer. This definitely saves steps! I guess I didn't want to go this way because when splitting it leaves a blank row, which of course I can filter out, so actually this makes more sense. 

However, I still don't know how to replace in bulk with not exact matches, so will leave this open and see if there is any other person with insight on this!

Thanks,

[HotChilli]

Can you post a data sample please?

[KarlWat]

Hi! So see attached:

https://docs.google.com/spreadsheets/d/1j5GRE9-P7Jq2aCRwTBbvgUeYlUrX8ZKdZsfXXbY-l7c/edit?usp=sharing

I tried posting the table here but it continued to mark it as error, then it wouldn't allow me to post then I forgot! anyway, what I do from source file is:

-Delete any spaces, and remove the "/" since I need to add it back in order to split the columns to have 1 number in each row, and sometimes the people who input the data won't add any "/" so the best I can do is have a string of numbers that go after the "VIN" letters, and the replace orders is basically running from 0 to 9  to add the "/" back so I can split columns.... any other way of doing it is appreciated!