Convert one column of data to four columns based on first word in each row.

[laganlee]

Hi all.

I have table with a single column which has rows of text.

Each row starts with either "Header", "Date :", "Name :", "Age :", "Height: "    followed by some text.

It is always in the same order.

I need four columns:  Date  |  Name  |  Age  | Height  

How is this done?

Really appreciate your time looking 🙂 

Table looks like this:

TextColumn

Header

Date : Wednesday 11th July 2022

Name : John Bull

Age : 34

Height : 177

Header

Date : Tuesday 14th July 2022

Name : Mary Smith

Age : 44

Height : 112

Header

Date : Monday 13th June 2022

Name : Mark Smith

Age : 56

Height : 180

[laganlee]

Thanks a lot! That worked first time 🙂 looks complicated but was easily implemented.

Much appreciated

[ronrsnfld · Accepted solution]

• Split the column by the colon
• Pivot with no aggregation
  • There are several ways to do this
  • I think the fastest is to use a custom function written by Cam Wallace as shown below

Custom Function

Rename "fnPivotAll"

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Main Function

let

//Change next line to reflect yourd actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextColumn", type text}}),

//Split on the colon, then Trim to get rid of any leading/trailing spaces
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "TextColumn", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"TextColumn.1", "TextColumn.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TextColumn.1", type text}, {"TextColumn.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"TextColumn.1", Text.Trim, type text}, {"TextColumn.2", Text.Trim, type text}}),

//Pivot with no aggregation using custom function
    Pivot = fnPivotAll(#"Trimmed Text", "TextColumn.1", "TextColumn.2"),
    #"Removed Columns" = Table.RemoveColumns(Pivot,{"Header"}),

//Transform Date column to "real dates"
    #"Text to Date" = Table.TransformColumns(#"Removed Columns", {"Date", each 
        let 
            split = Text.Split(_," "),
            #"Remove th" = Text.RemoveRange(split{1},Text.Length(split{1})-2,2),
            #"Replace in List" = List.ReplaceRange(split,1,1,{#"Remove th"}),
            recombine = Text.Combine(List.RemoveFirstN(#"Replace in List",1)," ")
            
        in 
            Date.FromText(recombine)}),

//set the data types
    #"Changed Type2" = Table.TransformColumnTypes(#"Text to Date",{{"Date", type date}, {"Name", type text}, {"Age", Int64.Type}, {"Height", Int64.Type}})
in
    #"Changed Type2"

[HotChilli]

You need to assign a number ID to each set of related data.  First, filter out the rows that start 'Header'. Then add an Index column.

Then add a custom column using Number.IntegerDivide to divide the Index Column by 4 (as you have groups of 4).

Then remove the Index column, split the first column by ':' .

Then Pivot the 1st column (pick the Values column and Don't Aggregate)

--

That's the method.  Have a go and I can help if you get stuck (There will be exact examples on this forum or the web too)