Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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ěď
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |