Forum Discussion
rows to columns
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 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 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"