Add rows dynamically in Power Query based on condition and transform columns

[naelske_cronos]

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

1. 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.
2. 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'.
3. 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

[AlienSx · Accepted solution]

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

[naelske_cronos]

Hello,

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

[AlienSx]

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.

[naelske_cronos]

Hello,

Thanks for the help getting started.

I will check how I can use column names instead of positions.

Kind regards