Generating a table from fixed parameters

[Anonymous]

Hello,
I am trying to create a table like the following that will be used as X-axis for other Y-axis table data I have (also seven columns).

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

So, as you can see the numbering start from 1, goes down 16 rows and then jump to column 2 with the value 17 and so on. Can I create a table in Power BI Desktop that generates this table from me based on the parameters I provide. The parameters will be mainly the window (in this case 16) ,the number of columns and rows. Thanks a lot.

[Ahmedx · Accepted solution]

try this final  code

[danextian]

Hi @Anonymous ,

If you are trying to create a data table using those parameters,  try this:

let
    Source = {1..(RowCount * ColumnCount)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Inserted Integer-Division" = Table.AddColumn(#"Converted to Table", "IntegerDivision", each Number.IntegerDivide([Column1]-1, RowCount) + 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Integer-Division",{{"Column1", type text}}),
    Custom1 = Table.FromColumns( List.Transform( {1..ColumnCount}, (x)=>  let  
t = Table.SelectRows(#"Changed Type",  each [IntegerDivision] = x)[Column1]
in t
), {"1"..Text.From(ColumnCount)})
in
    Custom1

Make sure to create these parameters:

ColumnCount = 7

RowCount = 32

Interval = 16

[Anonymous]

Hey, the output you have is different than what I shared.

[Ahmedx]

pls try cod power query

[Ahmedx]

pls try

[Anonymous]

This works but it's very manual. Thank you for your help.

[gmsamborn]

Hi @Anonymous 

If this table will never change, you could copy and paste from Excel to "Enter Data".