Separating Data within a field

[SamBrown17]

I have a custom field within my Table that has multiple heading and results

ContactTime: "Date",

ConractSent: "Date",

ReceivedTime: "Date",

This is all in one field and I am able to split the data but I want to create the Heading into column headings with the results in the respective rows below

Is that possible?

[gdbaerg]

Here's a quick GIF showing the PQ steps:

[amitchandak]

@SamBrown17 , I think part of the question is missing. Also not very clear. Can share a screenshot too

[SamBrown17]

Sorry first time posting

I have the above in my fields within a table, and I want to be able to isolate the text prior to ":" and have that as a heading of a column in a new table, and then the text after the ":" as the row results and then have that link to the original table so I can look at a job within a master table and see when the Assessment was Booked?

[SamBrown17]

So It starts out like this

I then split it,

But then want to create a new table so that I have the results of each milestone.

[gdbaerg · Accepted solution]

Hi @SamBrown17, is this the result you are looking for?result

steps in PQ:

steps in PQ

M-Code:

let
Source = Csv.Document(File.Contents("C:\Users\Admin\Desktop\Sam Brown17\SamBrown17.csv"),[Delimiter=",", Columns=1, Encoding=1252]),
#"Split into rows at each comma" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Replaced { with blank" = Table.ReplaceValue(#"Split into rows at each comma","{","",Replacer.ReplaceText,{"Column1"}),
#"Replaced } with blank" = Table.ReplaceValue(#"Replaced { with blank","}","",Replacer.ReplaceText,{"Column1"}),
#"Trim text to remove extra spaces" = Table.TransformColumns(#"Replaced } with blank",{{"Column1", Text.Trim, type text}}),
#"Split at leftmost colon" = Table.SplitColumn(#"Trim text to remove extra spaces", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
#"Split at rightmost period" = Table.SplitColumn(#"Split at leftmost colon", "Column1.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Column1.2.1", "Column1.2.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split at rightmost period",{"Column1.2.2"}),
#"Transpose Table" = Table.Transpose(#"Removed Columns"),
#"Use 1st row as headers" = Table.PromoteHeaders(#"Transpose Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Use 1st row as headers",{{"assessmentContactTime", type datetime}, {"assessmentRequestDate", type datetime}, {"assessment_bookedDate", type datetime}, {"assessmentAttendanceDueDate", type datetime}})
in
#"Changed Type"

[SamBrown17]

Is it possible to get a more detailed PQ step please, For the ability for me to fast repeat?

[SamBrown17]

You did it, thank you Worked well