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
Anonymous's avatar
Anonymous
Not applicable
6 years ago

HotChilli here is an example XLS... here is an example of original data

 

ColumnValue
31/01/20201
Joe Bloggs 
1412123 
12314124 
52342344 
1/02/20204
N/A 
1314234234 
N/A 
10203030 
31312330 
Joe Bloggs 
2/02/20205
3/02/20203
Jane Doe 
31/01/20205
N/A 
12312344 
1/02/20204
2/02/20203

 

Expected Output is this

 

DateUserValue
31/01/2020Joe Bloggs1
1/02/2020Joe Bloggs4
2/02/2020Joe Bloggs5
3/02/2020Joe Bloggs3
31/01/2020Jane Doe5
01/02/2020Jane Doe4
02/02/2020Jane Doe3

 

 

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. 

  • 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?