Transpose rows into columns into rows based on value in row

[Cristan]

Hey

I am trying to transpose rows into columns based on value in row:

So that every time a new number occurs in row 1, it moves to new row.
The outcome I'm looking for is this:

But the process needs to be automatic.

I am sorry for the bad explanation, I don't know how else to describe it, I hope it makes sense.

Thanks in advance!

 

[Cristan]

STOFNR	PROEVEDATO	MAENGDE	ATTRIBUT
5	2011-01-14	25​
5	2012-02-08	24​
5	2012-11-13	14,1​
5	2013-11-13	14​
5	2015-11-12	24,1​
5	2016-12-01	24​
5	2019-10-17	24,7​
13	2011-01-14	6,3​
13	2012-02-08	6,2​
13	2012-11-13	6,2​
13	2013-11-13	6,1​
13	2015-11-12	6,26​
13	2016-12-01	6,13​
13	2019-10-17	6,4​
15	2012-02-08	0,11​
25	2012-02-08	1,6​
35	2011-01-14	160​
35	2012-02-08	160​
36	2012-11-13	130​
36	2013-11-13	170​
36	2015-11-12	170​
36	2016-12-01	100​
36	2019-10-17	140​
36	2017-12-04	160​
50	2011-01-14	1,2​
50	2012-02-08	0,9​
50	2012-11-13	0,9​
50	2013-11-13	0,1​
50	2015-11-12	0,35​
50	2016-12-01	0,1​
50	2019-10-17	1,5​
53	2012-11-13	0,49180203​
53	2013-11-13	0,49180203​
58	2011-01-14	35​
58	2012-02-08	35​
58	2012-11-13	30​
58	2013-11-13	31​
58	2015-11-12	30​
58	2016-12-01	31​
58	2019-10-17	34​
58	2017-12-04	30​
59	2011-01-14	36,1​
59	2012-02-08	33,1​

 

[JGordon11]

In Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.Group(Source, {"STOFNR"}, {{"All", each _}}),
    tbl1 = Table.TransformColumns(tbl, {{"All", each Table.FromRows(Table.ToColumns(_))}}),
    max = List.Accumulate(tbl1[All], 0, (s,c)=> let tcc = Table.ColumnCount(c) in if tcc>s then tcc else s),
    tcn = List.Accumulate({1..max}, {}, (s,c)=> s & {"Column" & Number.ToText(c)}),
    tbl2 = Table.ExpandTableColumn(tbl1, "All", tcn),
    Result = Table.RemoveColumns(tbl2,{"STOFNR"})
in
    Result

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	STOFNR	PROEVEDATO	MAENGDE	ATTRIBUT		Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8
2	5	40557	25			5	5	5	5	5	5	5
3	5	40947	24			40557	40947	41226	41591	42320	42705	43755
4	5	41226	14,1			25	24	14,1	14	24,1	24	24,7
5	5	41591	14
6	5	42320	24,1			13	13	13	13	13	13	13
7	5	42705	24			40557	40947	41226	41591	42320	42705	43755
8	5	43755	24,7			6,3	6,2	6,2	6,1	6,26	6,13	6,4
9	13	40557	6,3
10	13	40947	6,2			15
11	13	41226	6,2			40947
12	13	41591	6,1			0,11
13	13	42320	6,26
14	13	42705	6,13			25
15	13	43755	6,4			40947
16	15	40947	0,11			1,6
17	25	40947	1,6
18	35	40557	160			35	35
19	35	40947	160			40557	40947
20	36	41226	130			160	160
21	36	41591	170
22	36	42320	170			36	36	36	36	36	36
23	36	42705	100			41226	41591	42320	42705	43755	43073
24	36	43755	140			130	170	170	100	140	160
25	36	43073	160
26	50	40557	1,2			50	50	50	50	50	50	50
27	50	40947	0,9			40557	40947	41226	41591	42320	42705	43755
28	50	41226	0,9			1,2	0,9	0,9	0,1	0,35	0,1	1,5
29	50	41591	0,1
30	50	42320	0,35			53	53
31	50	42705	0,1			41226	41591
32	50	43755	1,5			0,49180203	0,49180203
33	53	41226	0,49180203
34	53	41591	0,49180203			58	58	58	58	58	58	58	58
35	58	40557	35			40557	40947	41226	41591	42320	42705	43755	43073
36	58	40947	35			35	35	30	31	30	31	34	30
37	58	41226	30
38	58	41591	31			59	59
39	58	42320	30			40557	40947
40	58	42705	31			36,1	33,1
41	58	43755	34
42	58	43073	30
43	59	40557	36,1
44	59	40947	33,1
45
Sheet1

 

[Cristan]

Hey

Thank you for replying!

This is the result I'm looking for, but I cant seem to replicate it.
In my original i have made some changes before the result i sent you:

let
    Source = Excel.Workbook(File.Contents("\\PRO01\RDSdesktop$\cin\Desktop\Dataplatform og BI\Rune\2022-03-02-grundvandsanalyser (4).xlsx"), null, true),
    Sheet0_Sheet = Source{[Item="Sheet0",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet0_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"BOREHOLENO", type text}, {"KOMMUNE", type text}, {"BOR_ANVENDELSE", type text}, {"ANLAEGID", Int64.Type}, {"INDTNR", Int64.Type}, {"GRUMO_NR", type any}, {"XUTM32EUREF89", Int64.Type}, {"YUTM32EUREF89", Int64.Type}, {"PROEVEID", Int64.Type}, {"PROEVEDATO", type date}, {"STOFGRUPPE_KODE", Int64.Type}, {"STOFNR", Int64.Type}, {"MAENGDE", type number}, {"ATTRIBUT", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ID", "BOREHOLENO", "KOMMUNE", "BOR_ANVENDELSE", "ANLAEGID", "INDTNR", "GRUMO_NR", "XUTM32EUREF89", "YUTM32EUREF89", "PROEVEID", "STOFGRUPPE_KODE"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"STOFNR", "PROEVEDATO", "MAENGDE", "ATTRIBUT"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"STOFNR", Order.Ascending}})
in
    #"Sorted Rows"

but i think i got, what you sent me added to that code correctly:

let
    Source = Excel.Workbook(File.Contents("\\PRO01\RDSdesktop$\cin\Desktop\Dataplatform og BI\Rune\2022-03-02-grundvandsanalyser (4).xlsx"), null, true),
    Sheet0_Sheet = Source{[Item="Sheet0",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet0_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"BOREHOLENO", type text}, {"KOMMUNE", type text}, {"BOR_ANVENDELSE", type text}, {"ANLAEGID", Int64.Type}, {"INDTNR", Int64.Type}, {"GRUMO_NR", type any}, {"XUTM32EUREF89", Int64.Type}, {"YUTM32EUREF89", Int64.Type}, {"PROEVEID", Int64.Type}, {"PROEVEDATO", type date}, {"STOFGRUPPE_KODE", Int64.Type}, {"STOFNR", Int64.Type}, {"MAENGDE", type number}, {"ATTRIBUT", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ID", "BOREHOLENO", "KOMMUNE", "BOR_ANVENDELSE", "ANLAEGID", "INDTNR", "GRUMO_NR", "XUTM32EUREF89", "YUTM32EUREF89", "PROEVEID", "STOFGRUPPE_KODE"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"STOFNR", "PROEVEDATO", "MAENGDE", "ATTRIBUT"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"STOFNR", Order.Ascending}}),
    tbl = Table.Group(Source, {"STOFNR"}, {{"All", each _}}),
    tbl1 = Table.TransformColumns(tbl, {{"All", each Table.FromRows(Table.ToColumns(_))}}),
    max = List.Accumulate(tbl1[All], 0, (s,c)=> let tcc = Table.ColumnCount(c) in if tcc>s then tcc else s),
    tcn = List.Accumulate({1..max}, {}, (s,c)=> s & {"Column" & Number.ToText(c)}),
    tbl2 = Table.ExpandTableColumn(tbl1, "All", tcn),
    Result = Table.RemoveColumns(tbl2,{"STOFNR"})
in
    Result

But i get this error:

I hope this is enough info to make my problem clear.

Thank you for your help!

Best regards