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, t...
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"
Hi Imke,
Thank you for looking at this.
The pattern is derrived from the blank row. The row after a blank row is always Name, the row after that (if not blank) is always Place, and the row after that (if not blank) is always Job. They are never mixed up.
Chris
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"