[Power query] Transpose uneven data and combine multiple occurences by delimiter

[Dutsj]

So i got some rough data from an SQL, it is in JSON and it's poorly formatted. I've somehow managed to format it almost how i want it, now i'm just limited by my skills and i can't seem to find anyone with a similar issue. I've tried several things, split by list, group, transposing you name it, i just can't seem to get it right.

I've visualized some sample data because i think that explains it better. Basically i want to transpose the first columns rows into columns, and add the values. Everytime it gets to a new column 1, that means it's a new row. It's not identical, sometimes column4name will be missing, sometimes there's only 5 columnnames, other times there's duplicate columnnames that needs to be combined with a delimiter (comma and space ", "). The author and link columns are identical until the next column1, so they only need to be added once as a new column after the transposed columns.

Basically i want this data to be transformed into the table below it if possible.

ColumnNames	Values	Author (same value until next column1)	Link (same value until next column1)
Column1Name (column1 will always be start of a new row)	1​	Author1	http:/Link1.com
Column2Name	2​	Author1	http:/Link1.com
Column3Name	3​	Author1	http:/Link1.com
Column3Name (There can be duplicate column names per row, join by comma)	4​	Author1	http:/Link1.com
Column5Name	5​	Author1	http:/Link1.com
Column1Name (column1 will always be start of a new row)	a	Author2	http:/Link2.com
Column2Name	b	Author2	http:/Link2.com
Column3Name	c	Author2	http:/Link2.com
Column4Name	d	Author2	http:/Link2.com
Column5Name	e	Author2	http:/Link2.com
Column6Name	f	Author2	http:/Link2.com
Column7Name	g	Author2	http:/Link2.com
Column7Name	h	Author2	http:/Link2.com
Column7Name (There can be duplicate column names per row, join by comma)	i	Author2	http:/Link2.com
Column1Name (column1 will always be start of a new row)	101​	Author3	http:/Link3.com
Column2Name	102​	Author3	http:/Link3.com
Column3Name	103​	Author3	http:/Link3.com
Column3Name (There can be duplicate column names per row, join by comma)	104​	Author3	http:/Link3.com
Column5Name	105​	Author3	http:/Link3.com
Column6Name	106​	Author3	http:/Link3.com
Column7Name	107​	Author3	http:/Link3.com
Column8Name	108​	Author3	http:/Link3.com

So from the above table to this table below

Column1Name	Column2Name	Column3Name	Column4Name	Column5Name	Column6Name	Column7Name	Column8Name	Author	Link
1​	2​	3, 4	null	5​	null	null	null	Author1	http:/Link1.com
a	b	c	d	e	f	g, h, i	null	Author2	http:/Link2.com
101​	102​	103, 104	null	105​	106​	107​	108​	Author3	http:/Link3.com

 

[Dutsj]

The query becomes much simpler and I would assume much faster if you add four helper columns to the original table as in the example below:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	ColumnNames	Values	Author	Link	Multiple	Number	Joined	Keep		Column1Name	Column2Name	Column3Name	Column5Name	Column4Name	Column6Name	Column7Name	Column8Name	Author	Link
2	Column1Name	1	Author1	http:/Link1.com	FALSE	0	1	TRUE		1	2	3, 4	5					Author1	http:/Link1.com
3	Column2Name	2	Author1	http:/Link1.com	FALSE	0	2	TRUE		a	b	c	e	d	f	g, h, i		Author2	http:/Link2.com
4	Column3Name	3	Author1	http:/Link1.com	TRUE	1	3, 4	TRUE		101	102	103, 104	105		106	107	108	Author3	http:/Link3.com
5	Column3Name	4	Author1	http:/Link1.com	FALSE	0	4	FALSE
6	Column5Name	5	Author1	http:/Link1.com	FALSE	0	5	TRUE
7	Column1Name	a	Author2	http:/Link2.com	FALSE	0	a	TRUE
8	Column2Name	b	Author2	http:/Link2.com	FALSE	0	b	TRUE
9	Column3Name	c	Author2	http:/Link2.com	FALSE	0	c	TRUE
10	Column4Name	d	Author2	http:/Link2.com	FALSE	0	d	TRUE
11	Column5Name	e	Author2	http:/Link2.com	FALSE	0	e	TRUE
12	Column6Name	f	Author2	http:/Link2.com	FALSE	0	f	TRUE
13	Column7Name	g	Author2	http:/Link2.com	TRUE	2	g, h, i	TRUE
14	Column7Name	h	Author2	http:/Link2.com	TRUE	1	h, i	FALSE
15	Column7Name	i	Author2	http:/Link2.com	FALSE	0	i	FALSE
16	Column1Name	101	Author3	http:/Link3.com	FALSE	0	101	TRUE
17	Column2Name	102	Author3	http:/Link3.com	FALSE	0	102	TRUE
18	Column3Name	103	Author3	http:/Link3.com	TRUE	1	103, 104	TRUE
19	Column3Name	104	Author3	http:/Link3.com	FALSE	0	104	FALSE
20	Column5Name	105	Author3	http:/Link3.com	FALSE	0	105	TRUE
21	Column6Name	106	Author3	http:/Link3.com	FALSE	0	106	TRUE
22	Column7Name	107	Author3	http:/Link3.com	FALSE	0	107	TRUE
23	Column8Name	108	Author3	http:/Link3.com	FALSE	0	108	TRUE
Sheet1

Cell Formulas
Range	Formula
E2:E23	E2	=A2=A3
F2:F22	F2	=IF(E2,MATCH(FALSE,E3:E$23,0),0)
G2:G23	G2	=TEXTJOIN(", ",1,OFFSET(B2,0,0,F2+1))
H3:H23	H3	=IF(F2,FALSE,TRUE)
F23	F23	=IF(E23,MATCH(FALSE,E$23:E24,0),0)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilterKeep = Table.SelectRows(Source, each [Keep]),
    RemoveCols = Table.RemoveColumns(FilterKeep,{"Values", "Multiple", "Number","Keep"}),
    Pivot = Table.Pivot(RemoveCols, List.Distinct(RemoveCols[ColumnNames]), "ColumnNames", "Joined"),
    lstReorder = let tcn = Table.ColumnNames(Pivot) in List.Skip(tcn,2) & List.FirstN(tcn,2),
    ReorderCols = Table.ReorderColumns(Pivot,lstReorder)
in
    ReorderCols

Looks good, but i forgot to mention that i'm trying to do this only in Power BI so i'm not sure how i would achieve the helper columns in power query, i'm not very skilled in it yet ?

 

[Dutsj]

The query becomes much simpler and I would assume much faster if you add four helper columns to the original table as in the example below:

Looks good, but i forgot to mention that i'm trying to do this only in Power BI so i'm not sure how i would achieve the helper columns in power query, i'm not very skilled in it yet ?

Did you see my reply? I assume you don't have the answer since you didn't reply but i just wanted to make sure

 

[JGordon11]

I think the pivot step (tbl9) may be the slow part. If you stop at tbl8 is it a lot faster?

 

[Dutsj]

I think the pivot step (tbl9) may be the slow part. If you stop at tbl8 is it a lot faster?

Yea unfortunately, i've tried adding it step for step and it freezes on tbl9 after 10 minutes ?