Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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ěď
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |