Forum Discussion
rows to columns
I have a single column of data, that looks something like this:
Name 1
Place 1
Job 1
Name 2
Place 2
Name 3
Place 3
Job 3
Is there any way to turn this into a traditional table, that would like like this:
Name 1 Place 1 Job 1
Name 2 Place 2
Name 3 Place 2 Job 3
Many Thanks
Hi Anonymous ,
that makes much sense and would work very well if there are no cases where you have a job but no place:
Please check this code (paste it into the advanced editor and follow the steps):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVIrViVYKyElMhrG98pOgLDABVmWEpMoITc4YSc4YbgKQFQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"My Column" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"My Column", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), IdentifySplitter = Table.AddColumn(#"Added Index", "IsSplitter", each [My Column] = ""), GroupID = Table.AddColumn(IdentifySplitter, "CreateGroupID", each if [IsSplitter] then [Index] else null), #"Apply GroupID to elements" = Table.FillUp(GroupID,{"CreateGroupID"}), #"Removed Other Columns" = Table.SelectColumns(#"Apply GroupID to elements",{"My Column", "CreateGroupID"}), #"Grouped Rows1" = Table.Group(#"Removed Other Columns", {"CreateGroupID"}, {{"Count", each Table.RowCount(_), type number}, {"All Rows", each _, type table [My Column=text, CreateGroupID=number]}}), CreateRecord = Table.AddColumn(#"Grouped Rows1", "Record", each Record.FromList([All Rows][My Column], List.Transform({1..[Count]}, Text.From))), #"Removed Other Columns1" = Table.SelectColumns(CreateRecord,{"Record"}), #"Expanded Record" = Table.ExpandRecordColumn(#"Removed Other Columns1", "Record", {"1", "2", "3"}, {"Name", "Place", "Job"}) in #"Expanded Record"
4 Replies
- I know I've seen ImkeF solve this problem although for the life of me I can never reproduce it on my own.
Hi Anonymous ,
conceptually, this is possible. But it would require that there is some logic that determines that "Name 3" is a name and not the job for no2.
So for the sample values you've given, one could extract the value after the blank space and retrieve the corresponding item number, but this is probably not possible for the actual values that come in.
But if my assumption is wrong and there is a pattern that can determine to which category (future column name) the items belong, please provide that data.
Hi Anonymous ,
that makes much sense and would work very well if there are no cases where you have a job but no place:
Please check this code (paste it into the advanced editor and follow the steps):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVIrViVYKyElMhrG98pOgLDABVmWEpMoITc4YSc4YbgKQFQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"My Column" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"My Column", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), IdentifySplitter = Table.AddColumn(#"Added Index", "IsSplitter", each [My Column] = ""), GroupID = Table.AddColumn(IdentifySplitter, "CreateGroupID", each if [IsSplitter] then [Index] else null), #"Apply GroupID to elements" = Table.FillUp(GroupID,{"CreateGroupID"}), #"Removed Other Columns" = Table.SelectColumns(#"Apply GroupID to elements",{"My Column", "CreateGroupID"}), #"Grouped Rows1" = Table.Group(#"Removed Other Columns", {"CreateGroupID"}, {{"Count", each Table.RowCount(_), type number}, {"All Rows", each _, type table [My Column=text, CreateGroupID=number]}}), CreateRecord = Table.AddColumn(#"Grouped Rows1", "Record", each Record.FromList([All Rows][My Column], List.Transform({1..[Count]}, Text.From))), #"Removed Other Columns1" = Table.SelectColumns(CreateRecord,{"Record"}), #"Expanded Record" = Table.ExpandRecordColumn(#"Removed Other Columns1", "Record", {"1", "2", "3"}, {"Name", "Place", "Job"}) in #"Expanded Record"
