Copy to the column by the number of rows (Query Editor)

[skopcak]

it would be possible in the query editor to do this?

Quantity	Code	Code 2	result
1	1a	4/17	1a (4/17)
2	2b	4/17	2b (4/17)
1	3c	4/17	2b (4/17)
3	4d	4/17	3c (4/17)
3	5e	4/17	4d (4/17)
3	6f	4/17	4d (4/17)
			4d (4/17)
			5e (4/17)
			5e (4/17)
			5e (4/17)
			6f (4/17)
			6f (4/17)
			6f (4/17)

Thank you answer

[v-ljerr-msft]

Hi @skopcak,

At first, I thought this could be achieved using some Table functions(M) like Table.SelectRows, and Table.Repeat etc. And I have tried a lot, but still can't work it out(trying to studying M currently). @ImkeF, could you help us?

Regards

[Anonymous · Accepted solution]

Here's one approach that might go close...

Create a function "NewList":

(Quantity, Code, Code2) =>
let
 NewList = List.Generate(()=> 
 // Start your counter at 0 and seed your list "Result" with your required values
 [QtyCntr = 0, Result = {Code & " (" & Code2 & ")"}], 
 // As you increment the counter to the limit of Quantity, build up another value set
 each [QtyCntr] < Quantity, each [QtyCntr=[QtyCntr]+1, Result = {Code & " (" & Code2 & ")"}], 
 //Return each value set (one for each Quantity)
 each [Result])
in
 NewList

Create a Table2 to reference your source Table1, calls the function for each row, and expands the lists of lists out:

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each NewList([Quantity],[Code],[Code 2])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Expanded Custom", "Custom")
in
    #"Expanded Custom1"

Then tidy your columns as needed...

[skopcak]

Hello, Thank you for your reply, it works great. I'm very grateful.

[skopcak]

by bylo možné v dotazu editor, jak to udělat?

Množství	Kód	kód 2	výsledek
1	1a	4/17	1a (4/17)
2	2b	4/17	2b (4/17)
1	3c	4/17	2b (4/17)
3	4d	4/17	3c (4/17)
3	5e	4/17	4d (4/17)
3	6f	4/17	4d (4/17)
			4d (4/17)
			5e (4/17)
			5e (4/17)
			5e (4/17)
			6f (4/17)
			6f (4/17)
			6f (4/17)

Děkujeme, že jste odpověď