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 types of data are:

  • Dates
  • User Names
  • NA values (to ignore)
  • Invoice numbers (also to ignore)

I had thought of doing something with filters like you have. Would it be possible to have the filter for user name dynamically change based on a separate list - like a mapping file?

  • sanimesa's avatar
    sanimesa
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    Anonymous   A possible way to solve this would be to use calculated columns. The assumption here is that the value appears next to the date. What makes it a bit ambiguous is that the occurrence of name is not consistent, do we pick the pevious non-blank name or the next? Also, you do not have test data with different names, so not sure if everything is correct. 

     

    In any case, here are the steps:

     

    1. Add an index to the table (Transfom Data)

    2. Add the below columns:

     

    Possible Name = IF(ISBLANK(Messy[Value]),  IF(AND( ISERROR((ISNUMBER(VALUE(Messy[Column])))), Messy[Column]<>"N/A"), Messy[Column], ""), "")

Prev Idx = 
 CALCULATE (
    MAX(Messy[Index] ),
    FILTER (
       ALL(Messy),
       NOT(ISBLANK(Messy[Possible Name])) && 
       Messy[Possible Name] <> "" &&
       Messy[Index]< EARLIER(Messy[Index]))
    )

Next Idx = 
 CALCULATE (
    MAX(Messy[Index] ),
    FILTER (
       ALL(Messy),
       NOT(ISBLANK(Messy[Possible Name])) && 
       Messy[Possible Name] <> "" &&
       Messy[Index]> EARLIER(Messy[Index]))
    )

Picked Idx = IF(ISBLANK(Messy[Prev Idx]), Messy[Next Idx], Messy[Prev Idx])

Final Name = CALCULATE(MAX(Messy[Possible Name]), FILTER(ALL(Messy), Messy[Index]=EARLIER(Messy[Picked Idx])))

     

     

    3. Filter the table where the value column is non-blank. 

     

    This is how it looks for me.