Splitting out data from a single cell into multiple columns

[mnt23]

Good morning all, 

I have some data that I am importing into Power BI desktop and am using Power Query to transform it into something useful. 

The data comes from a Power Query online form, that our teams complete remotely, and it then populates a Sharepoint list. It's that Sharepoint list that I'm linking to to get the data. 

The data is essentially telling me when various people are sent to a task, start the task, finish the task and then recover back. Unfortuntaely, the way it was set up, this information is all in one cell. It shows a timestamp and then the status (employed, recoered etc). A photo of what the data looks like is here. As you can see, there can be a lot of entries in the cell as they update it daily and individuls can be on task for months. 

Is there a way for me to separate out the data so that it tells me the date and time that they change status? I.e. move frmo JMC to employed to recovered etc. 

[mnt23]

Hi Vijay, 

Thanks, that worked.

I do still have a couple of issues though.

Some rows there is still a date stamp in the "status" column - is this because there is a different delimiter that has got in somehow? 

Result after current transformations.

Cells before transformations - the top 2 cells format correctly post transformation, the bottom one doesn't.

Also, you'll see that int eh first photo there are some errors post tranformation. Those are as a result of the data being input incorrectly. Is there a way to autmatically remove any rows that have either a null value or an error value in those columns?

[Vijay_A_Verma]

Can you copy and paste some sample data in Excel and post Excel to Onedrive/Googledrive and post the link here? Make sure to include the rows which give out errors. 

Only that column which contains these data will do good.

[mnt23]

Hi Vijay, I don't suppose you've had a chance to look at this?

[Vijay_A_Verma · Accepted solution]

The errors are due to following entries which don't follow the protocol. I have handled these now and worked out file can be downloaded from here - https://1drv.ms/x/s!Akd5y6ruJhvhuhBymtSFvgi2jtGl?e=33JnxK 

[mnt23]

Ah ok, that's frustrating. Thanks for your help, I think I should be able to manually edit those values in the source data to solve it. Thanks!

[mnt23]

I sure can. I've added a couple of comments on the first few rows, but feel free to delete that if it's not helpful. 

Data

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLWNzbUNzIwMlIwsLQyNVQISk3OL0stSk2JyTPWN0JIGRgouObm5FempijF6kQrOQG1GukbGkHlTa2MjRRKUotLoNLOQGkzhLSFlYk5sslAKQOIlKEpyGQvX2eQoCXCNjOQmFJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Daily Update" = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Daily Update", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Daily Update"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Daily Update", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date", "Time", "Status"})
in
    #"Split Column by Delimiter1"

[mnt23]

Hi Vijay, 

Thanks for your help. Running a test with a blank query that looks like it's going to give me what I need. However, I'm struggling to use the code in my actual data - I'm very inexperienced when it comes to using power query!

The current code in the advanced editor is this:

let
    Source = SharePoint.Tables("https://test.sharepoint.com/teams/22740/CentralLists", [Implementation=null, ApiVersion=15]),
    #"155216eb-e730-45a7-bc21-06fe6bcdb4fb" = Source{[Id="155216eb-e730-45a7-bc21-06fe6bcdb4fb"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"155216eb-e730-45a7-bc21-06fe6bcdb4fb",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

I'm not sure how to then add in the tranformation you've given me the code for. If I just delete everything then it can't find the column - I'm assuming that the current code tranforms the data to give it the column headings in the first place. 

How am I supposed to add in a second transformation?

[Vijay_A_Verma]

Use this

let
    Source = SharePoint.Tables("https://test.sharepoint.com/teams/22740/CentralLists", [Implementation=null, ApiVersion=15]),
    #"155216eb-e730-45a7-bc21-06fe6bcdb4fb" = Source{[Id="155216eb-e730-45a7-bc21-06fe6bcdb4fb"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"155216eb-e730-45a7-bc21-06fe6bcdb4fb",{{"ID", "ID.1"}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Daily Update", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Daily Update"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Daily Update", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date", "Time", "Status"})
in
    #"Split Column by Delimiter1"