Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 

 

View solution in original post

4 REPLIES 4
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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

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

 

 

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

skopcak
Helper I
Helper I

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors