Messy data handling in 1 column

Thanks for replying.  Are you able to post data as per previous requests?

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

Expected Output is this

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.

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?

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. 

Hi,

This data does not make any sense whatsoever.  For 2/2/2020 and 3/2/2020, there are no names in your source data.  So then why should there be Joe Bloggs against these 2 dates?

Thanks Ashish_Mathur. The names are not meant to be lined up against the dates - that's the reason for my posting. If things were lined up fine then I would have been able to solve it but unfortunately the data does not come out like that.

Take a look at the table again. I edited the data so you can see what happens when the next user name comes up in the last. In this case, I added Jane Doe. The rules are that the top section refers to Joe Bloggs, the next user is Jane Doe. The date values are the only row where value data is recorded against.

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?