Forum Discussion

naelske_cronos's avatar
naelske_cronos
Advocate II
1 year ago
Solved

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...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago
    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
AlienSx's avatar
AlienSx
Super User
1 year ago

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's avatar
naelske_cronos
Advocate II
1 year ago

Hello,

 

Thanks for the help getting started.

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

 

 

Kind regards