Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
naelske_cronos
Helper III
Helper III

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 DateAdvanceInvoice

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

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

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

View solution in original post

4 REPLIES 4
AlienSx
Super User
Super User

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

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

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.

Hello,

 

Thanks for the help getting started.

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

 

 

Kind regards

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.