rows to columns

[Anonymous]

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

[ImkeF]

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.

[Anonymous]

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

[ImkeF · Accepted solution]

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"

[Greg_Deckler]

I know I've seen @ImkeF solve this problem although for the life of me I can never reproduce it on my own.