cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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

QuantityCodeCode 2result
11a4/171a (4/17) 
22b4/172b (4/17)
13c4/172b (4/17)
34d4/173c (4/17)
35e4/174d (4/17)
36f4/174d (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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

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

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

 

 

View solution in original post

4 REPLIES 4
Highlighted
Helper I
Helper I

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

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

MnožstvíKódkód 2výsledek
11a4/171a (4/17) 
22b4/172b (4/17)
13c4/172b (4/17)
34d4/173c (4/17)
35e4/174d (4/17)
36f4/174d (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ěď

Highlighted
Microsoft
Microsoft

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

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 currentlySmiley LOL). @ImkeF, could you help us?

 

Regards

Highlighted
Solution Sage
Solution Sage

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

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

 

 

View solution in original post

Highlighted
Helper I
Helper I

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

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors