Question on Using Power Query to Convert Column to Table

[ziggle]

Hi folks,

I frequently get data in column format that I need to convert to a table. For example, I get a single column of data like this:

Tom
1
2
3
****
4
5
6
Harry
7
8
9

I want it in the following form:
Name C1 C2 C3
Tom 1 2 3
**** 4 5 6
Harry 7 8 9

Historically, I convert the column data to table form using (1) the offset function, or (2) Microsoft Word's text-to-table function. I am now using Power Query, which I like very much, but I do not know how to do this conversion in PQ. Any ideas?

Zig

 

[SimonNU]

If your goal is to load this into a data model then wouldn't a tabular layout be better? Something like:

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Name", each try if Logical.From( [Column1] ) then null else null otherwise [Column1]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Name] <> [Column1]))
in
    #"Filtered Rows"

 

[SimonNU]

Below is a solution. I don't have time to clean it up at the moment though, sorry!

High level: the goal is to take each name's numbers, concatenate them and then split them.

Input table:

Column1
Tom
1
2
3
4
****
5
6
7
Harry
8
9
10

<tbody>
</tbody>

Script:

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Name", each try if Logical.From( [Column1] ) then null else null otherwise [Column1]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Name] <> [Column1])),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Name", "Column1"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Name"}, {}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows",{"Name"},#"Reordered Columns",{"Name"},"NewColumn",JoinKind.LeftOuter),
    #"Added Custom1" = Table.AddColumn(#"Merged Queries", "Select Column", each Table.Column([NewColumn], "Column1")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Transform([Select Column], each Text.From(_))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.1", each Text.Combine([Custom], ",")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Name", "Custom.1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns","Custom.1",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1.1", Int64.Type}, {"Custom.1.2", Int64.Type}, {"Custom.1.3", Int64.Type}, {"Custom.1.4", type text}})
in
    #"Changed Type1"

Output:

Name	Custom.1.1	Custom.1.2	Custom.1.3	Custom.1.4
Tom	1	2	3	4
****	5	6	7
Harry	8	9	10

<tbody>
</tbody>

** EDIT: #"Split Column by Delimiter" will need to be made dynamic **

 

[ziggle]

Absolutely fantastic! I can clean it up from here. This also is an excellent example of M-code for me to work through. Again, thanks a bunch.

Zig

 

[anvg]

Hi
It is another way a little

let
    source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NameCol = Table.AddColumn(source,"name", each if [Column1] is text then [Column1] else null, type text),
    Filled = Table.FillDown(NameCol, {"name"}),
    Filtered = Table.SelectRows(Filled, each [name]<>[Column1]),
    Grouped = Table.Group(Filtered, {"name"}, { 
    {"tables", each Table.FromRows({_[Column1]}), type table},
    {"len", each List.Count([Column1]), Int64.Type}
    }),
    ColIds = List.Numbers(1, List.Max(Grouped[len]), 1),
    expandColNames = List.Transform(List.Positions(ColIds), each "Column" & Text.From(ColIds{_})),
    preResult = Table.SelectColumns(Grouped, {"name","tables"}),
    Result = Table.ExpandTableColumn(preResult, "tables", expandColNames)
in
    Result

Regards,

 

[ImkeF]

Hi anvg,
what a nice move in Grouped. With this, you could actually shorten your code considerably:

let
    source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    NameCol = Table.AddColumn(source,"name", each if [Column1] is text then [Column1] else null, type text),
    Filled = Table.FillDown(NameCol, {"name"}),
    Grouped = Table.Group(Filled, {"name"}, {{"tables", each Table.FromRows({_[Column1]}), type table}}),
    Custom1 = Table.Combine(Grouped[tables])
in
    Custom1

 

[anvg]

Hi

you could actually shorten your code considerably

Oh, thank you very much!
Regards,