Powerquery - converting rows to columns based on condition

[Guinaba]

Hi all,

I have the following scenario:
How can I split the col. B depending on the number of values? For example, below I need to have another col C to place the second value.

A B

 

[jdellasala]

Bringing this into Power Query

Book1
	A	B
1	Table1
2	Item	Value
3	Heineken Lgr 330mL BT4x6 BP	1201976
4	Heineken Lgr 330mL BT4x6 BP	1257179
5	Heineken Lgr 500mL CN4x6 CP	1254837
6	Ice Beer 375mL CN30 MP	1200517
Sheet1

Unfortunately, Split by Text to Number isn't as flexible as a simple Split by Delimiter, so had to do it in a couple of steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitByLetterToNumber = Table.SplitColumn(Source, "Item", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Item.1", "Item.2", "Item.3", "Item.4"}),
    SplitBySpace = Table.SplitColumn(SplitByLetterToNumber, "Item.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Item.2.1", "Item.2.2"}),
    MergedML = Table.CombineColumns(SplitBySpace,{"Item.1", "Item.2.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    MergedDetail = Table.CombineColumns(MergedML,{"Item.2.2", "Item.3", "Item.4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.1"),
    RenamedColumns = Table.RenameColumns(MergedDetail,{{"Merged", "Item"}, {"Merged.1", "Detail"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Value", Int64.Type}})
in
    ChangedType

Resulting in this table

Book1
	D	E	F
1	Table1Split
2	Item	Detail	Value
3	Heineken Lgr 330mL	BT4x6 BP	1201976
4	Heineken Lgr 330mL	BT4x6 BP	1257179
5	Heineken Lgr 500mL	CN4x6 CP	1254837
6	Ice Beer 375mL	CN30 MP	1200517
Sheet1

Hope that's what you were thinking of!

 

[JGordon11]

Maybe this?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupRows = Table.Group(Source, {"Item"}, {{"All", each _}},0),
    MaxCols = List.Accumulate(GroupRows[All], 0, (s,c)=> let trc = Table.RowCount(c) in if trc > s then trc else s),
    AddListColumn = Table.AddColumn(GroupRows, "Lists", each [All][Value] & List.Repeat({null}, MaxCols - Table.RowCount([All]))),
    ColNames = {"Item"} & List.Accumulate({1..MaxCols}, {}, (s,c)=> s & {"Value." & Text.From(c)}),
    DeleteAllColumn = Table.RemoveColumns(AddListColumn, "All"),
    TableRows = List.Transform(Table.ToRows(DeleteAllColumn), each {_{0}} & _{1}),
    Result = Table.FromRows(TableRows, ColNames)
in
    Result

Book2
	A	B	C	D	E	F	G	H
1	Table1			Query Output
2	Item	Value		Item	Value.1	Value.2	Value.3
3	A	5		A	5	3	6
4	A	3		B	1	4
5	A	6		C	2
6	B	1
7	B	4
8	C	2
9
Sheet1