Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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"
Thanks a lot! That worked first time 🙂 looks complicated but was easily implemented.
Much appreciated
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"
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)