Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Messy data handling in 1 column

Hi everyone, I've got a file format I'm trying to handle which puts a whole bunch of different information into the 1 column.   It has date fields - eg 31/07/2018  It has user names  It has invoi...
Need Help
HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
6 years ago

Power Query code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc4xCoAwDAXQq5TMhTY/8QA6OniB0lG6CA7eH4yKbQVJl7zwk6ZEwiFyQEQkT0zZJ5r31U3bXsph5G5iZTCk9ZCLtMIAUXsNbC3etXrLEsY2tfiTqPQZW06sKogFIB38/BHdxeFJdSKU8wk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type date}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Value] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "User", each "Joe Bloggs")
in
    #"Added Custom"

Notice that I cheated on the User column.

Do the simplest thing that works is usually my motto.

If your data only has one user I'll be very surprised.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you!

There are different users... the data types are:

  • Dates
  • Usernames
  • NA values (ignore)
  • Invoice numbers (also to ignore)

I thought I'd do something with filters like you. Would it be possible for the filter for the user name to change dynamically based on a separate list, such as a mapping file?