Incorrectly formatted input SAP data

[Krays23]

HI,

Some of my logistics team at work cant type properly nor should they need to type as i supplied barcodes to get this data correct.

Basically the Kanban location data is a mess and needs to be cleaned up some how. What i am looking for is an easy way to fix the formatting properly

Each location should be formatted the same such as FBU - A330 Aileron - 05 now as you can see from the photo some people have been putting FBU - A330 Aileron-05

Is there a way to have query editor check all the entry’s and adjust.

Thanks

Dan

[Melissa]

Hi @Krays23,

Give this a go, just paste it in a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnMKVdBVcDQ2NlBwzMxJLcrPU9A1MFWK1cEmhSSDU0JBF6cEqhYFoqwh0jAMGaiEEW49ChC5WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Kanban location" = _t]),
    FormatValues = Table.TransformColumns(Source, {"Kanban location", each Text.Combine(List.Transform( Text.Split(_, "-"), each Text.Trim(Text.From(_))), " - "), type text})
in
    FormatValues

I hope this is helpful.

[Krays23]

HI Melissa,

Didnt work that well

i cant send you the file as its huge.

[Melissa]

Hi @Krays23,

What didn’t work well?
Just supply a representative sample in XLSX

[Krays23]

Sorry my bad figured it out thank you