cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

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

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)

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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...

4 REPLIES 4
Microsoft

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
Not applicable

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...

Helper I

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

Helper I

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ěď

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors