Forum Discussion

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
2 years 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
    2 years 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
Icon for Super User rankSuper User
2 years 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
naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
2 years ago

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's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years 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
      Icon for Advocate II rankAdvocate II
      2 years ago

      Hello,

       

      Thanks for the help getting started.

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

       

       

      Kind regards