Help with looping through an entire lists in Power Query

[Kibitokai]

Hello!

I am pulling data from the internet right now and formatting it into a readable list using power query, but I am running into an issue. When I split the columns into rows (as I have to change the data from columns to rows for it to be more easily read) I am having to do it manually with a formula like the one below:

= Table.SplitColumn(rc1, "list", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"list.1", "list.2", "list.3", "list.4", "list.5", "list.6", "list.7", "list.8", "list.9", "list.10", "list.11", "list.12", "list.13", "list.14", "list.15", "list.16", "list.17", "list.18", "list.19", "list.20", "list.21", "list.22", "list.23", "list.24", "list.25", "list.26", "list.27", "list.28", "list.29", "list.30", "list.31", "list.32", "list.33", "list.34", "list.35", "list.36", "list.37", "list.38", "list.39", "list.40", "list.41", "list.42", "list.43", "list.44", "list.45", "list.46", "list.47", "list.48", "list.49", "list.50", "list.51", "list.52", "list.53", "list.54", "list.55", "list.56", "list.57", "list.58", "list.59", "list.60", "list.61", "list.62", "list.63", "list.64", "list.65", "list.66", "list.67", "list.68", "list.69", "list.70", "list.71", "list.72", "list.73", "list.74", "list.75", "list.76", "list.77", "list.78", "list.79", "list.80", "list.81", "list.82", "list.83", "list.84", "list.85", "list.86", "list.87", "list.88", "list.89", "list.90", "list.91", "list.92", "list.93", "list.94", "list.95", "list.96", "list.97", "list.98", "list.99", "list.100", "list.101", "list.102", "list.103", "list.104", "list.105", "list.106", "list.107", "list.108", "list.109", "list.110", "list.111", "list.112", "list.113", "list.114", "list.115", "list.116", "list.117", "list.118", "list.119", "list.120", "list.121", "list.122", "list.123", "list.124", "list.125", "list.126", "list.127", "list.128", "list.129", "list.130", "list.131", "list.132", "list.133", "list.134", "list.135", "list.136", "list.137", "list.138", "list.139", "list.140", "list.141", "list.142", "list.143", "list.144", "list.145", "list.146", "list.147", "list.148", "list.149", "list.150", "list.151", "list.152", "list.153", "list.154", "list.155", "list.156", "list.157", "list.158", "list.159", "list.160", "list.161", "list.162", "list.163", "list.164", "list.165", "list.166", "list.167", "list.168", "list.169", "list.170", "list.171", "list.172", "list.173", "list.174", "list.175", "list.176", "list.177", "list.178", "list.179", "list.180", "list.181", "list.182", "list.183", "list.184", "list.185", "list.186", "list.187", "list.188", "list.189", "list.190", "list.191", "list.192", "list.193", "list.194", "list.195"})

This works fine for when I only have 195 records or less however some of the data sets I have contain over 600 records and one contains over 2700 records so I am wondering if there is any way to loop through the columns instead of manually creating a list like the one above because typing out an extra 2000+ items would not be worth it. If anyone needs to see the entire PQ formula I can share that as well, thank you!

 

[GraH]

On the UI you can split into rows...

In Excel you can then create a pivot with this field in the "column" section.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    delim = ",",
    maxcols = List.Accumulate(Source[list], 0, (s,c)=> 
        if c <> null then (let cc = Text.From(c), n = Text.Length(cc) - Text.Length(Text.Remove(cc, delim))+1 in if n>s then n else s) else s ),
    colnames = List.Transform({1..maxcols}, each "list." & Text.From(_)),
    Result = Table.SplitColumn(Source, "list", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), colnames)
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	list		Name	list.1	list.2	list.3	list.4	list.5	list.6	list.7
2	A	1,2		A	1	2
3	B	1,2,4		B	1	2	4
4	C			C
5	D	1,2,6		D	1	2	6
6	E	1,2,7,8		E	1	2	7	8
7	F	abc		F	abc
8	G	1,2,9		G	1	2	9
9	H	1,2,10		H	1	2	10
10	I	1,2,11,12,13,14,15		I	1	2	11	12	13	14	15
11	J	1,2,12		J	1	2	12
12	K			K
13	L	1,2,14		L	1	2	14
14	M	def		M	def
15	N	1,2,16		N	1	2	16
16	O	1,2,17		O	1	2	17
17
Sheet1