Forum Discussion

SamBrown17's avatar
SamBrown17
Helper I
5 years ago
Solved

Separating Data within a field

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?

 

 

Tips and Tricks
  • gdbaerg's avatar
    gdbaerg
    5 years ago

    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"

7 Replies

    • SamBrown17's avatar
      SamBrown17
      Helper I
      5 years ago

      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's avatar
        SamBrown17
        Helper I
        5 years ago

        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.