Forum Discussion
Add rows dynamically in Power Query based on condition and transform columns
Hello,
I have a table in Power query which looks like this:
| Code | Delivery Phase | Approval Date | Advance | Invoice | Advanced Payment |
| 24008 | Deellevering1 | 17/05/2024 | 1950 | 20803,6 | 23/01/2024 |
| 24008 | Deellevering2 | 21/06/2024 | 6409,47 | ||
| 24006 | Deellevering1 | 21/06/2024 | 2490 | 28809,56 | 23/01/2024 |
- If the column 'Advance' is not null, a new row should be added for that specific 'Code' and 'Delivery phase' otherwise no rows are added.
- In addition a new column should be added called 'Type Approval' and has the values 'Invoice' or 'Advance', again only when the column 'Advance' is not null, it should have the value 'Advance' otherwise 'Invoice'.
- As you can see, the column 'Advance' is not needed anymore as the value will be added in the new row in the existing column 'Invoice', same for the values in the column 'Advanced Payment' which goes into the existing column 'Approval Date'.
| Code | Delivery Phase | Type Approval | Approval Date | Invoice |
| 24008 | Deellevering1 | Invoice | 17/05/2024 | 20803,6 |
| 24008 | Deellevering1 | Advance | 23/01/2024 | 1950 |
| 24008 | Deellevering2 | Invoice | 21/06/2024 | 6409,47 |
| 24006 | Deellevering1 | Invoice | 21/06/2024 | 28809,56 |
| 24006 | Deellevering1 | Advance | 23/01/2024 | 2490 |
I have tried using the List.Repeat but I didn't go any further as my experience in Power Query is basic.
Thanks in advance!
Kind regards
let Source = your_table, f = (x) => {{x{0}, x{1}, "Invoice", x{2}, x{4}}} & (if x{5} is null then {} else {{x{0}, x{1}, "Advance", x{5}, x{3}}}), result = Table.FromRows( List.Combine(Table.ToList(Source, f)), {"Code", "Delivery Phase", "Type Approval", "Approval Date", "Invoice"} ) in result
4 Replies
let Source = your_table, f = (x) => {{x{0}, x{1}, "Invoice", x{2}, x{4}}} & (if x{5} is null then {} else {{x{0}, x{1}, "Advance", x{5}, x{3}}}), result = Table.FromRows( List.Combine(Table.ToList(Source, f)), {"Code", "Delivery Phase", "Type Approval", "Approval Date", "Invoice"} ) in resultHello,
Thanks for the quick response.
This already seems pretty difficult to me. It works with my example but I was wondering:
- Which data type does the 'x' parameter expects? Is that a table data type like the source?
- Do the numbers stand for the index on the column? Is it also possible to give the name of the column? Suppose I have more than 30 columns and sometimes those columns can differ which means the index could also change?
Thanks!
Kind regards
Hello, naelske_cronos
1. "x" is a list. List of row values. That's how nice little Table.ToList works.
2. Numbers in {} stand for positions of values in "x" list.
3. If you want to work with column names then maybe Table.ToRecords + List.Transform (or List.Generate) and Table.FromRecords afterwards.
