Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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...
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
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.
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ěď
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
81 | |
79 | |
58 | |
35 | |
34 |
User | Count |
---|---|
99 | |
59 | |
56 | |
46 | |
40 |